Geek Answers Handbook

T-SQL for every alternative?

I need to take data from one table and import it into another table. In the pseudo code, 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 far as I understand, T-SQL does not support For Every..Next, so what are my alternatives?

+7
sql foreach sql-server tsql
source share
7 answers

Other things being equal, it is better to establish operations based on.

 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 )
+7
source share

If you are using SQL Server 2008, you can use the MERGE . 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 )
+9
source share

You can use the cursor to do this as described by others. Personally, I like to do two statements per line as follows:

 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)
+4
source share

doing this in a while loop is simply wrong.
for your situation, you can use the new MERGE statement in sql server 2008.
Here is a simple example on how to do this.

+4
source share

If you are using SQL Server 2008, the best way to do this is to MERGE . 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 ...
+4
source share

You specify TSQL but do not provide a version. If you are on SQL2008, Merge should do what you need.

+3
source share

One of the most common ways is to use cursors. Thus, you can view each record returned by your query and process it accordingly using UPDATE or INSERT.

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

0
source share

More articles:


All Articles