SQL Server

Merge



This is new command in SQL Server 2008. It was not availabled initially in SQL Server 2008 but later on introduce in SQL Server 2008. It provides support for update and insert and delete on target table in same query and its a cool benefit for progammers.


CREATE TABLE test (
id INT,
code VARCHAR(100))

INSERT INTO test
VALUES (1,'a1')
INSERT INTO test
VALUES (2,'a2')

SELECT *
FROM test


CREATE TABLE newtest (
id INT,
code VARCHAR(100))

INSERT INTO newtest
VALUES (1,'b1')
INSERT INTO newtest
VALUES (3,'a3')


Now we want to insert data from newtest to test table.
When id is matching we will update data
When id is not matching we will insert that data



Using Merge statement

SELECT *
FROM test

SELECT *
FROM newtest

MERGE test t
USING newtest n
ON t.id = n.id
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, code)
VALUES (n.id, n.code)
WHEN MATCHED THEN
UPDATE SET
id = n.id,
code = n.code;



Rules for WHEN MATCHED

• One statement must be update and second must be delete.

• If first one will fail only then second will execute

• Both will be processed in order.

Rules for WHEN NOT MATCHED BY TARGET

• TARGET is default option
  
• We can specify WHEN NOT MATCHED ALSO.
• It says that there are some rows in Source table which are not in Target table
• This statement can be only used for INSERT.
• This statement can be used only once

Rules for WHEN NOT MATCHED BY SOURCE

• It says there are rows in Target which are not in Source table.
• We can specify it for two times one for UPDATE and one for DELETE.
• If first will fail only then second will execute.