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 given a task where you are asked to validate the data between two tables tables as source and target a following conditions:
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 the SQL logic for such task, definitely you need to write three different SQL statement to perform insert, update, and delete based on matching values between source and target table.
However, you can do it with single statement that is SQL MERGE statement rather writing three separate statement.
Lets try to understand this with the help of following example, first we create a two sample tables named EMP_SRC ( a source table) and EMP_TRGT ( a target table) and insert some records.
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 contains some records and the ID column in both table will be used to 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 target 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.*;
Also Read..
1,837 total views, 6 views today