Load table from the result of function in execute sql task
Hi, i have a funciton in execute sql task and the output of the function gives the destination table to be loaded, a select script selecting the no of fields from source and sourceconnectionstring. For example : select table_dest, SourceSQL from dbo.fn_GetData(1001) Result : table_dest : Customer (col1, col2) SourceSQL : Select col1, col2 from CustomerInfo how do i load Customer table from the select script in variable SourceSQL? I have variables table_dest and SourceSQL in Result set of the execute sql task for function dbo.fn_GetData
June 27th, 2012 12:52pm

What do you want to do with the resultset? Do you want to iterate throught, put the whole result into an object variable. Read this variable in a ForEachLoop. In this ForEachLoop you will dirct the columns to your table_dest ans SourceSQL. If you get only one row, go to the resultset tab in your ExecuteSQL and direct the columns to your variables.
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2012 3:52pm

I would just create some dynamic SQL: DECLARE @table_dest VARCHAR(100);DECLARE @SourceSQL VARCHAR(100);SELECT @table_dest = table_dest, @SourceSQL = SourceSQL FROM dbo.fn_GetData(1001);DECLARE SQLStatement VARCHAR(300); SET SQLStatement = 'INSERT INTO ' + @table_dest + ' ' + @SourceSQL + ';' EXEC(SQLStatement); MCTS, MCITP - Please mark posts as answered where appropriate.
June 27th, 2012 5:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics