sql-serversql-server-2008-r2on-duplicate-keymultiple-insert

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?


Solution

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