MERGE Statement in SQL Server 2008

MERGE Statement in SQL Server 2008

By : Kasim Wirama, MCITP, MCDBA

In TSQL area, SQL Server 2008 has some enhancements; one of them is ability to wrap several DML statement into one by MERGE statement. Syntax for MERGE statement is :

MERGE <target table> [AS <target table alias>]
USING <source table/query/view> [AS <source alias name>]
ON <join condition>
WHEN MATCHED THEN
    <UPDATE or DELETE statement>
WHEN NOT MATCHED BY TARGET THEN
    <INSERT statement>
WHEN NOT MATCHED BY SOURCE THEN
    <UPDATE or DELETE statement>

Example how to use MERGE statement is given below.

You have stocks stored at Stock table here :

CREATE TABLE Stock (Name varchar(10) primary key, Qty int);
GO

Insert into Stock VALUES (‘Indosat’,10000);
Insert into Stock VALUES (‘BNI’,15000);

Every day you trade stocks and those transactions are put into Trade table here :

Insert into Trade (Name varchar(10) primary key, Qty int);

For example, during a day, you sold 5000 Indosat stocks, bought 5000 BNI stocks, and bought new stocks of BPMIGAS and those are recorded into Trade table.

Insert into Trade VALUES (‘Indosat’,-5000);
Insert into Trade VALUES (‘BNI’, 5000);
Insert into Trade VALUES (‘BPMIGAS’,2000);

At the end of a day, you would like to update Stock table with transactions from Trade using MERGE statement with condition when new stock on Trade, they will be inserted into Stock table, when existing stock then they will be updated into Stock table. Here is MERGE query :

MERGE Stock AS s
USING Trade AS t
ON s.Name = t.Name
WHEN MATCHED THEN
   UPDATE SET Stock.Qty += Trade.Qty
WHEN NOT MATCHED BY TARGET THEN
   INSERT VALUES (t.Name, t.Qty);

After above MERGE statement is run, rows on Stock table will be :

Name                    Qty
Indosat                 5000
BNI                         20000
BPMIGAS            2000

You can write MERGE query here:

MERGE Stock AS s
USING Trade AS t
ON s.Name = t.Name
WHEN MATCHED THEN
   UPDATE SET Stock.Qty += Trade.Qty
WHEN NOT MATCHED THEN
   INSERT VALUES (t.Name, t.Qty);

By default if you don’t specify BY SOURCE or BY TARGET on WHEN NOT MATCHED clause, it will be default to BY TARGET.