Loop through a temporary table and insert to another table
I need to insert the data in a csv file in to a temporary table and insert some data for the corresponding id value in another table. I have created and inserted the data in to the csv file. For all the records in the csv file how can I loop through and insert dome data for the corresponding record in the other table.
CREATE TABLE #tbcompanies (ID INT) GO BULK INSERT #tbcompanies FROM 'd:\ids.csv' WITH ( ROWTERMINATOR = '\n' ) select * from #tbcompanies drop table #tbcompanies
Assuming both tables have an ID column, you could update the other table like:
update ot set col1 = tmp.col1 . col2 = tmp.col2 from @tbcompanies tmp join OtherTable ot on ot.ID = tmp.ID
If in addition to updating, you'd like to insert rows that do not exist, consider the merge statement:
; merge OtherTable as target using #tmpcompanies as source on target.id = source.id when not matched by target then insert (id, col1, col2) values (source.id, source.col1, source.col2) when matched then update set col1 = source.col1, col2 = source.col2;
You don't need to loop through anything, since you use SQL Server 2008 and this version supports the MERGE statement.
Have a look here.
Or simply use update with a from clause and join the two tables.
If it's upsert functionality you need, I strongly recommend the Merge function.
merge TargetTableName target using #tbcompanies tmp on tmp.idfield=target.idfield when matched then update...... when not matched then insert...........