SQL Server MERGE statement is used to modify data in a target table based on data in a source table.
The statement joins the target to the source by using a column common to both tables.
Using Merge statement you can insert, update and delete the data from the target table through only single statement -according
to how the rows match up as a result of the join.
Syntax
MERGE target_table USING source_table ON condition WHEN MATCHED THEN update_statement WHEN NOT MATCHED BY TARGET THEN insert_statement WHEN NOT MATCHED BY SOURCE THEN DELETE;
MERGE AND USING – Source and Target table are specified to merge statement by Using keyword.
CONDITION – Source table are matched to the rows from the target table. It is similar to the join condition in the join clause.
Condition could have three possible result set as given below
MATCHED – When the rows match the merge condition. For the matching rows, It updates the rows columns in the target table
with values from the source table.
NOT MATCHED BY TARGET – When the rows from the source table that do not have any matching rows in the target table.
In this case, It adds the rows from the source table to the target table.
NOT MATCHED BY SOURCE – when the rows in the target table that does not match any rows in the source table.
If you want to validate the target table with the data from the source table, then you use this match condition to delete rows from the target table.
Lets look at an example of Merge in SQL Server.
Suppose, you have two tables – a source and target and you want to validate target table data with source table for three cases as given below.
Update records, If target table have matching rows with source table then update the rows in the target table with the values coming from the source table.
Insert records, If source table do not have matching rows with target table then insert rows that are in the source table into the target table.
Delete records, target table has some rows that do not exist in the source table then delete rows from target table to maintain the data integrity between source and target table.
To implement SQL logic for such requirement, definitely you need to write three different SQL statement to perform insert, update, and delete based on matching values between source and traget table.
However, you can do it with single statement using MERGE rather writing three statement.
Lets try to understand it with some example .
CREATE TABLE EMP_SRC (ID INT, NAME VARCHAR(30), CODE VARCHAR(10)) CREATE TABLE EMP_TRGT (ID INT, NAME VARCHAR(30), CODE VARCHAR(10)) INSERT INTO EMP_SRC(ID, NAME, CODE) VALUES(1, 'A', 'A12'), (2, 'B', 'B12'), (3, 'C','C12'), (4, 'D','D12') INSERT INTO EMP_TRGT(ID, NAME, CODE) VALUES(1, 'D', NULL), (2, 'Y', 'Y12'), (5, 'G','G12'), (6, 'E','E12')
Here we have a source table (EMP_SRC) and a target table (EMP_TRGT) as given below .
As you can see , source and target table both have some records and the ID column in both table as the merge condition.
For ID=1 and 2, The rows from source table EMP_SRC matches with the rows from the target table EMP_TRGT, so the merge statement updates the values in NAME, and CODE columns in the EMP_TRGT table.
For ID =3 and 4, The rows from the source table EMP_SRC do not exist in the target table EMP_TRGT, so the merge statement insert this rows into the target table.
For ID = 5 and 6, The rows from the Target table EMP_TRGT do not exist in source table EMP_SRC, so the merge statement deletes these rows from target table EMP_TRGT.
You can also refer the below diagram to understand the MERGE statement flow.
MERGE EMP_TRGT AS TRGT
USING EMP_SRC AS SRC ON SRC.ID = TRGT.ID
WHEN MATCHED
THEN UPDATE SET TRGT.NAME = SRC.NAME, TRGT.CODE = SRC.CODE
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, NAME, CODE) VALUES (SRC.ID, SRC.NAME, SRC.CODE)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Now you can check EMP_SRC and EMP_TRGT table records.
SELECT * FROM EMP_SRC SELECT * FROM EMP_TRGT
As you can see, Merge statement made the changes in traget table EMP_TRGT based on matching values in source table EMP_SRC.
You can also capture the data for records that are insert, update, and delete using merge statement , that can be done using OUTPUT clause.
The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statements,
The OUTPUT clause gives access to two virtual tables that are called as Magic Tables. These are
- INSERTED contains the new rows (INSERT or UPDATE‘s SET)
- DELETED contains the old copy of the rows(UPDATE‘s SET)
So using OUTPUT clause with Merge statement, you can see the inserted, updated, and deleted records as a result.
MERGE EMP_TRGT AS TRGT USING EMP_SRC AS SRC ON SRC.ID = TRGT.ID WHEN MATCHED THEN UPDATE SET TRGT.NAME = SRC.NAME, TRGT.CODE = SRC.CODE WHEN NOT MATCHED BY TARGET THEN INSERT (ID,NAME, CODE) VALUES (SRC.ID, SRC.NAME, SRC.CODE) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.*, inserted.*;
