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 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 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 do not match any rows in the source table. If you want to validate the data in target table with the data in the source table, then you use this not match condition to delete rows from the target table.
Lets look at an example of using Merge statement in SQL.
Suppose, you got a task where you have to validate the data between two tables one is source and other is target table based on the following conditions:
Update records: If target table has 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 does not have matching rows with target table then insert rows which are in the source table into the target table.
Delete records: If target table has any row/rows which does not exist in the source table then delete those rows from target table to maintain the data integrity between source and target table.
To implement the SQL logic for such conditions, definitely you need to write three different SQL statement to perform insert, update, and delete operations based on matching values between source and target table.
However, you can do it using a single statement that is SQL MERGE statement rather than writing three separate T-SQL 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, a source and target table both contains some records and the ID column is the common column in both table which will be using in 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 for the NAME, and CODE columns in the EMP_TRGT table.
For ID =3 and 4, the rows from the source table EMP_SRC does not exist in the target table EMP_TRGT, so the merge statement insert rows for these IDs into the target table.
For ID = 5 and 6, the rows from the Target table EMP_TRGT does not exist in source table EMP_SRC, so the merge statement delete 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 has 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 inserted, updated, and deleted using merge statement, it 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..
