SQL Server: multiple INSERT with MERGE

Question

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?

Aaronaaronic

Answer 1

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 );

Gluteus

Answer 2

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.

Adai