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

Answers


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.

pseudo code

   merge TargetTableName target
   using #tbcompanies tmp on tmp.idfield=target.idfield
   when matched then update......
   when not matched then insert...........

Need Your Help

Getting stackoverflow exception while inserting images in gridview

android gridview android-viewpager viewpagerindicator

I have a view pages inside it there are fragments with gridview and images(Emojis) inside gridview. it's like an activity with emojis and you can swipe between different emoji types.

Recalling information from dictionaries within dictionaries

python python-3.x dictionary nested

So say I have a number of dictionaries within a dictionary