SQL Server 2008 MERGE

MERGE statement performs insert,update or delete operations on target table based on the results of a join with source table.

Here is a simple example

--Staging table

CREATE TABLE Staging

(ID int, Color varchar(10))

GO

--Real table

CREATE TABLE RealTable

(ID int, Color varchar(10))

GO

--Populate Staging table

INSERT INTO Staging VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

GO

--Merge statement

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

--Test the update

UPDATE Staging SET Color = 'Pink' WHERE ID = 3

GO

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

--Test the delete

DELETE FROM Staging WHERE ID = 2

GO

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

DROP TABLE RealTable

GO

DROP TABLE Staging

GO