Multiple values to store in multiple variables

  • In my project i have to read many values from the database without having any relation like this .

    select top 1 order_id from orders

    select top 1 product_id from product

    select top 1 master_id from master_detais

    .....................................................

    Like the above statements i have hundreds of tables to read the data from different tables .

    now i want to store each result set in a variable (Each result always contains a single value)

    Here i dont want to use Execute SQL task or Script task because as i mentioned there are hundreds of statement like above so i am not preferring to take execute SQl task for each result set.

    Please Help me to find generic approach so that using one task i can achieve the above requirement.

  • Why this scenario? What are you going to do with hundreds of seperate variables?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I have to export these values to a flat file...

  • Wouldn't it be easier to write a SQL query that retrieves all those values in one statement (you can use the TQSL UNION clause to link different statements together) in an OLE DB Source in the dataflow and then just export it to the flat file using a flat file destination?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Before exporting i have some other data (XYZ ) from a derived columns taks which need to be concatenated . to this data mentioned in the requirement and then finally export to a flat file.

  • simhadriraju (2/7/2012)


    Before exporting i have some other data (XYZ ) from a derived columns taks which need to be concatenated . to this data mentioned in the requirement and then finally export to a flat file.

    Yes? So you do that in the dataflow, using another derived column task?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • what should be the solution in that case.

    Existing Derived columns contains the data in a expression

    (DT_STR,12,1252)RecordNumber +Amount 1 + Amount 2

    For this above expression i have to add the result like below

    Resultset1+(DT_STR,12,1252)RecordNumber + Amount1 + Amount2 +

    Resultset2+Resultset3 .....

    so can any one help me how to acheive this output

  • So if I'm not mistaken, you need to take one record from each table (a random one, as no ORDER BY is specified), and you need to concatenate it to one long string and then put it into a flat file?

    I wonder what the business value for that scenario is...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Yes Exactly .. Please help me to achieve this ...

  • Why would you want to take one random record of each table and put it in a flat file?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • sorry we will take max(id) from each table

  • simhadriraju (2/7/2012)


    sorry we will take max(id) from each table

    That makes a bit more sense.

    The problem here is generalizing it. Since every table has a different column name, this is quite difficult. If all those columns would have been named ID, it would have been pretty easy.

    So I guess you're basically stuck with writing hundreds of different SELECT statement or write a dynamic SQL statement that will check each table and locate the column that ends with ID (what if you have multiple columns that end with ID?).

    Once you get the results in some sort of table format, you can concatenate the values together using the methods described in this article:

    Concatenating Row Values in Transact-SQL

    [/url]

    The FOR XML method is probably the fastest, but you'll need to test it.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • But we cannot concatenate all the resultsets .

    Because my requirement is

    somevalue + resultset1 + Somevalue2 + Somevalue3 + resultset3 +

    somevalue + resultset4

    Here the somevalue could be a random sequence which we cannot guess Example as i explained above later on time business people may ask to add resultset1 at the end of resultset4 ... .

    Please help me ..........

  • I Request all of the members please help me it's really critical and urgent task for me ..

  • simhadriraju (2/7/2012)


    Because my requirement is

    somevalue + resultset1 + Somevalue2 + Somevalue3 + resultset3 +

    somevalue + resultset4

    This is not the same as:

    simhadriraju (2/7/2012)


    Resultset1+(DT_STR,12,1252)RecordNumber + Amount1 + Amount2 +

    Resultset2+Resultset3 .....

    What do you mean with the following?

    simhadriraju (2/7/2012)


    Here the somevalue could be a random sequence which we cannot guess

    simhadriraju (2/7/2012)


    as i explained above later on time business people may ask to add resultset1 at the end of resultset4 ... .

    --> you never explained this.

    Let's do this the right way. Read the link in my signature about posting questions.

    Post table definitions, sample data and desired output.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply