Populating the field values in a table SSIS
I have created a stored procedure which retrives the values from a table in a database and based on result populates another table. This stored procedure is used in the SSIS package created using Execute SQL Task. All my logic about populating the new table in maintained in the stored procedure instead of in the package. My stored procedure logic is some what described as below. Step 1 : INSERT INTO TEMP TABLE SELECT col1, col2, col3 FROM TABLE1 Step 2 : INSERT INTO TABLE2 ( COLUMN1, COLUMN2, COLUMN3 ) VALUES ( (SELECT C1 FROM TABLE3 WHERE TABLE3.COL1 = TEMP.COL1) , (SELECT C2 FROM TABLE 4 WHERE TABLE4.COL1 = TEMP.COL2) , (SELECT C3 FROM TABLE 5 WHERE TABLE5.COL1 = TEMP.COL3) ) Is this logic the correct way of populating the fields??
July 11th, 2011 5:11am

you sql query can be shorten to this query with same result: INSERT INTO TABLE2 ( COLUMN1, COLUMN2, COLUMN3 ) select table3.c1, table4.c2, table5.c3 from table1 inner join table3 on table1.col1=table3.col1 inner join table4 on table1.col2=table4.col1 inner join table5 on table1.col3=table5.col1 http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
July 11th, 2011 5:18am

Hi, I cannot use the joins. As there are approximately 100 columns in TABLE1 to be populated with each of the column source is from a different table. Also the column values can be populated with blank values if there the select query of any of the column returns no rows.
July 11th, 2011 5:23am

But in your first query , you used join either! also you can use left outer joins to handle empty and null values too. like this: INSERT INTO TABLE2 ( COLUMN1, COLUMN2, COLUMN3 ) select table3.c1, table4.c2, table5.c3 from table1 left outer join table3 on table1.col1=table3.col1 left outer join table4 on table1.col2=table4.col1 left outer join table5 on table1.col3=table5.col1 and my question is: what are you looking for? for a ssis method for this operation ? or you want to make your t-sql code better? this post is second one, but if you want first one, you should use lookup transform or merge join transform, which will need lots of works for about 100 joins!http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
July 11th, 2011 5:28am

I was trying to optimise my stored procedure which is used in SSIS package. My stored procedure logic is some thing like this : INSERT INTO TABLE2 ( COLUMN1, COLUMN2, COLUMN3 ) VALUES ( (SELECT TOP 1 C1 FROM TABLE3 WHERE TABLE3.COL1 = TEMP.COL1) , (SELECT TOP 1 C2 FROM TABLE 4 WHERE TABLE4.COL1 = TEMP.COL2) , (SELECT TOP 1 C3 FROM TABLE 5 WHERE TABLE5.COL1 = TEMP.COL3) ) I have included the top 1 criteria in the SELECT query.
July 11th, 2011 5:41am

so, you can use this query: (Note that your query can not be optimized very much when you have to join 100 times ,and your reference tables hasn't good records which forced you to fetch top1 of them , with these circumstances your query is enough for working , maybe you need a refactoring at all but for short answer your query or below query will work in available situation) INSERT INTO TABLE2 ( COLUMN1, COLUMN2, COLUMN3 ) select table3.c1, table4.c2, table5.c3 from table1 left outer join table3 on table1.col1=table3.col1 left outer join table4 on table1.col2=table4.col1 left outer join table5 on table1.col3=table5.col1 group by table3.c1,table4.c2,table5.c3 http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
July 11th, 2011 6:28am

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

Other recent topics Other recent topics