Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 323
Petr Hubík Avatar asked Apr 08 '14 00:04

Petr Hubík


People also ask

Which is faster insert or MERGE?

The basic set-up data is as follows. We've purposely set up our source table so that the INSERTs it will do when merged with the target are interleaved with existing records for the first 500,000 rows. These indicate that MERGE took about 28% more CPU and 29% more elapsed time than the equivalent INSERT/UPDATE.

Can we use multiple tables in MERGE statement?

Merging tables by columns. Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).

How insert multiple values in SQL insert?

Insertion in a table is a DML (Data manipulation language) operation in SQL. When we want to store data we need to insert the data into the database. We use the INSERT statement to insert the data into the database.


1 Answers

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 );
like image 56
onedaywhen Avatar answered Nov 10 '22 06:11

onedaywhen