February 7, 2012 at 12:05 am
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.
February 7, 2012 at 12:43 am
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
February 7, 2012 at 1:30 am
I have to export these values to a flat file...
February 7, 2012 at 1:35 am
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
February 7, 2012 at 1:39 am
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.
February 7, 2012 at 1:42 am
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
February 7, 2012 at 1:49 am
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
February 7, 2012 at 2:21 am
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
February 7, 2012 at 2:49 am
Yes Exactly .. Please help me to achieve this ...
February 7, 2012 at 2:59 am
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
February 7, 2012 at 3:25 am
sorry we will take max(id) from each table
February 7, 2012 at 3:37 am
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
February 7, 2012 at 4:02 am
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 ..........
February 7, 2012 at 4:46 am
I Request all of the members please help me it's really critical and urgent task for me ..
February 7, 2012 at 4:53 am
simhadriraju (2/7/2012)
Because my requirement issomevalue + 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