CODEWITHSUNDEEP
sql-serversql-server-2008-r2on-duplicate-keymultiple-insert
Petr Hubík
Petr Hubík

Reputation: 191

SQL Server: multiple INSERT with MERGE

What is the method to do multi insert of values into SQL Server database? Usually in MySQL I use queries like:

INSERT INTO table (column1, column2)
VALUES(value1, value2), (value3, value4)
ON DUPLICATE KEY UPDATE column2 = VALUES(value2);

Is there a way how to do multiple insert into SQL Server with MERGE?

Upvotes: 10

Views: 10540

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

MERGE INTO YourTable T
   USING ( VALUES ( 'value1', 'value9' ), 
                  ( 'value3', 'value4' ) ) AS S ( column1, column2 )
      ON T.column1 = S.column1
WHEN MATCHED THEN 
   UPDATE
      SET column2 = S.column2
WHEN NOT MATCHED THEN
   INSERT ( column1, column2 ) VALUES ( column1, column2 );

Upvotes: 14

shree.pat18
shree.pat18

Reputation: 21757

Not sure if I get your question exactly right, but you can use the MERGE statement in T-SQL as follows:

  1. If the item in source does not match item in target on specified fields, insert into target.
  2. If the item in source matches item in target on specified fields, update the other fields in target with corresponding values from source.

In your case, the merge would be something like this:

merge yourtable as target
using
(select value1 a, value2 b
 union
 select value3 a, value4 b) as source
 on target.column1 = source.a
 when matched and target.column2 <> source.b
 then update 
 set target.col2 = source.b
 when not matched by target
 then
 insert (column1, column2) values (source.a, source.b);

This example uses hardcoded values, but it works when you are inserting from another table as well.

Upvotes: 0

Related Questions