Data Solutions: November 2010

SQL SERVER 2008 has introduced MERGE statement which is very helpful in synchronizing two tables.We can perform INSERT, UPDATE, or DELETE operations on target table based on the result of the join with the source table.

Well Syntax for MERGE is very complex so I will use a very simple example where I will create two table SOURCE and TARGET and then perform insert, update and delete on SOURCE table and then synchronize TARGET table using MERGE Statement.

1. Lets create two tables Source and Target with same schema and data and after updates in Source we will sync it with Target table.

CREATE TABLE SOURCE
(
ID INTEGER,
Name VARCHAR(50),
);
GO
INSERT INTO SOURCE
( ID, Name)
VALUES
(1, 'Rahul'),
(2, 'Mark'),
(3, 'Jen');

SELECT * INTO TARGET FROM SOURCE;

2. Lets modify data in Source table ( Delete , update and Insert new record).

DELETE FROM SOURCE
    WHERE ID =2;
--Update in source
UPDATE Source
    SET Name = 'Jason'
WHERE Id = 3;

-- new record in source
INSERT INTO SOURCE
    (ID, Name)
VALUES
    (4, 'Antonia');

3. Now comes the MERGE Statement
Source table will be joined with Target table on ID and then WHEN clause is used to identify type of changes
a.Update: Where both ID match but other columns doesnt match.
MATCHED and Target.Name <> Source.Name
b. New/Inserts: When IDs are not matched by Target
c. Deletes: When IDs are not matched by Source.

MERGE INTO TARGET
USING (SELECT * from Source) AS SOURCE
ON Target.ID = Source.ID
WHEN -- upadate
    MATCHED and Target.Name <> Source.Name THEN
        UPDATE
        SET Name = SOURCE.Name
WHEN -- new record in source
    NOT MATCHED BY TARGET THEN
        INSERT ( ID ,Name)
        VALUES ( Source.ID,Source.Name)
WHEN --records deleted in source
    NOT MATCHED BY SOURCE THEN
        DELETE
--see action
OUTPUT $action
    , Inserted.ID AS InsertedID
    , Inserted.NAME AS InsertedName
    , Deleted.ID AS DeletedID
    , Deleted.Name AS DeletedName;