MERGE Statement in MS SQL Server

Sometime we need to update one table based on the values of another table. Which table need to be updated is called target table or destination table and from which table data will be updated is called source table. There may be three scenarios:

1. The source table may have some same rows as that are in the destination table. However, these rows have different values which need to be updated into destination table from the source table.
2. The source table may have some rows that do not exist in the destination table which need to be inserted into  the destination table.
3. The destination table may have has some rows that do not exist in the source table which need to be deleted from the destination table.

In this tutorial, we will learn how to use the SQL Server MERGE statement to make changes in a table based on matching values from another table. MERGE statement synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. Here is the syntax of the MERGE statement:

MERGE destination_table 
USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

We have to specify the destination table after MERGE  clause and the source table after USING clause. The merge_condition determines how the rows from the source table are matched to the rows from the destination table. It is similar to the join condition in the join clause. Generally we use the key columns either primary key or unique key for matching. The merge_condition has three states: MATCHED, NOT MATCHED and NOT MATCHED BY SOURCE. Let’s see each of them:

• MATCHED: If the rows of the source table matched with the destination table then columns mentioned in the update_statement of destination table will be updated with values from the source table.
• NOT MATCHED:NOT MATCHED is also known as NOT MATCHED BY DESTINATION. If the rows of the source table that does not have any matching rows in the destination table then columns mentioned in the insert_statement from the source table will be inserted into the destination table.
• NOT MATCHED BY SOURCE: If the rows of the destination table that does not match any rows of the source table then rows of the destination table will be deleted.

Let’s think we have two tables emp_offline and emp_online that keep records of employee information.

CREATE TABLE emp_offline 
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

INSERT INTO emp_offline (id,name,email) VALUES
(1,'Fahim','fahim@gmail.com'), 
(3,'Karim','karim@gmail.com'), 
(4,'Rahim','rahim@gmail.com'), 
(100,'Hakim','hakim@gmail.com');

CREATE TABLE emp_online (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

INSERT INTO emp_online (id,name,email)
VALUES(1,'Fahim','fahim@gmail.com'),
(2,'Alim','alim@gmail.com'),
(3,'Karim','karim@yahoo.com'),
(4,'Rahim','rahim@hotmail.com');

To update data into the destination table: emp_offline with the values from the source table: emp_online, we use the following MERGE statement:

MERGE emp_offline d 
USING emp_online s
ON (d.id = s.id)
WHEN MATCHED
    THEN UPDATE SET 
        d.name = s.name,
        d.email = s.email
WHEN NOT MATCHED 
    THEN INSERT (id,name,email)
         VALUES (s.id,s.name,s.email)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

After executing the above MERGE statement, final emp_offline (destination) table will be updated as follows:

Description of the MERGE operation:

• id columns will be used as identifier column for both tables as the merge condition.
• The rows with id 1, 3, 4 from the emp_online table matches with the rows from the emp_offline table, therefore, the MERGE statement updates the values in name & email columns in the emp_offline table.
• The row with id 2 from the emp_offline table do not exist in the emp_online table, so the MERGE statement inserts this row into the emp_offline table.
• The row with id 100 from the emp_offline table does not exist in the emp_online table, therefore, the MERGE statement deletes this row from emp_offline table.

After merge operation, the data in the emp_offline table is fully synchronized with the data in the emp_online table. I would say it one of the important and most useful statement in SQL. You can bookmark this page for your reference.