SQL Server: multiple INSERT with MERGE

Petr Hubík picture Petr Hubík · Apr 8, 2014 · Viewed 8.5k times · Source

I'm trying to write bridge between MySQL database and SQL Server 2008 R2 to sync values in tables. I got stuck on multi insert 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?

Thanks for answers

Answer

onedaywhen picture onedaywhen · Apr 8, 2014
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 );