CODEWITHSUNDEEP
sql-serversql-server-2008
Dean MacGregor
Dean MacGregor

Reputation: 18661

What's the most efficient syntax to use Merge to upsert many rows at once?

There's 2 ways I've found of upserting many rows into a table with SQL Server 2008.

One of which is found here http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx says to create a temp table, then insert values to temp table, and finally merge that table with target able.

This doesn't seem very efficient to me because you have to create a table, fill the table, merge to target table, and then delete the temp table.

The only other thing I can think of is as follows...

MERGE dbo.targettable as tgt
USING (
SELECT 12 as col1, 13 as col2, 'abc' as col3, 'zyx' as col4
UNION ALL
SELECT 11 as col1, 11 as col2, 'def' as col3, 'def' as col4
(etc etc)
UNION ALL
SELECT 7 as col1, 10 as col2, 'jfj' as col3, 'tub' as col4)
as new
ON tgt.col1=new.col1
WHEN MATCHED THEN UPDATE SET tgt.col2=new.col2, tgt.col3=new.col3, tgt.col4=new.col4
WHEN NOT MATCHED THEN INSERT (col1, col2, col3, col4)
VALUES(new.col1, new.col2, new.col3, new.col4);

Based on usr's answer I was able to find http://msdn.microsoft.com/en-us/library/bb510625.aspx

I think this is the way to do it. Could someone verify that this syntax appears correct?

MERGE dbo.targettable as tgt
USING (VALUES(12, 13, 'abc', 'zyx'), (11, 11, 'def', 'def'),(7, 10, 'jfj', 'tub'))
AS new (col1, col2, col3, col4)
ON tgt.col1=new.col1
WHEN MATCHED THEN UPDATE SET tgt.col2=new.col2, tgt.col3=new.col3, tgt.col4=new.col4
WHEN NOT MATCHED THEN INSERT (col1, col2, col3, col4)
VALUES(new.col1, new.col2, new.col3, new.col4);

Upvotes: 1

Views: 689

Answers (1)

usr
usr

Reputation: 171216

Where does the data to be merged come from?

  • If it comes from a query, inline the query into the merge.
  • If it comes from the app, use table-valued parameters.
  • If it is generated iteratively, use a temp table or table variable.
  • If it is a constant like in your example use the VALUES clause. Don't use UNION ALL because it is more verbose, does not document semantics nicely and increases query compile time because the optimizer has to convert it to VALUES form.

Upvotes: 1

Related Questions