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

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 )