T-SQL For Each Alternative?

Question

I need to take data from one table and import it into another table. In pseudocode, something like this:

For Each row in table1
If row.personid is in table2 then
   update table2.row
Else
   insert row into table2
End If
Next

What is the best way to do this in T-SQL? As I understand it T-SQL doesn't support For Each..Next, so what alternatives do I have?

Answer 1

One of the most common ways is to use cursors. That way you can go through each record that your query returns and handle it accordingly, either with an UPDATE or INSERT.

See: http://msdn.microsoft.com/en-us/library/ms180169.aspx

Answer 2

You could use a cursor for this as others have described. Personally I like doing two statements in a row like so:

UPDATE tbl2 SET field1=tbl1.field1, field2=tbl1.field2 -- etc.
FROM tb12
JOIN tbl1 on tbl2.personid = tbl1.personid

INSERT tbl2 (personid, field1, field2)
SELECT personid, field1, field2 
FROM tbl1
WHERE NOT EXISTS (select personid from tbl2 where personid = tbl1.persondid)

Answer 3

All things being equal, set based operations are better.

update t1
set t1.x = t2.x
.
.
.
from table1 t1
inner join table2 t2 on t1.id = t2.t1id

then

insert into table1
select * from table2 t2 where t2.t1id not in (select table1.id from table1 )

Answer 4

doing this in a while loop is just wrong.
for your situatuin you can use the new MERGE statement in sql server 2008.
Here's a simple example on how to do it.

Answer 5

You state TSQL but don't give a version. If you are on SQL2008 the Merge statement should do what you need.

Answer 6

If you're on SQL Server 2008 then the best way to do this is with the MERGE statement. Something like...

MERGE INTO target_table t
USING source_table s
ON t.personid = s.personid
WHEN MATCHED THEN
    UPDATE ...
WHEN NOT MATCHED THEN
    INSERT ...

Answer 7

If you're using SQL Server 2008 then you could use the MERGE statement. Maybe something like this:

MERGE table2 AS t  -- target
USING table1 AS s  -- source
    ON ( t.personid = s.personid )
WHEN MATCHED THEN 
    UPDATE
    SET second_column = s.second_column,
        third_column = s.third_column,
        etc = s.etc
WHEN NOT MATCHED THEN    
    INSERT ( personid, second_column, third_column, etc )
    VALUES ( s.personid, s.second_column, s.third_column, s.etc )