CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Joshua
Joshua

Reputation: 2295

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

Upvotes: 2

Views: 3174

Answers (3)

Andomar
Andomar

Reputation: 238296

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;

Upvotes: 6

Me.Name
Me.Name

Reputation: 12544

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...........

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25397

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.

Upvotes: 1

Related Questions