MERGE Statement in SQL Server to insert, update and delete at the same time

The MERGE statement is used to make changes in one table based on values matched from another.   It can be used to combine insert, update, and delete operations into one statement. The MERGE statement basically merges data from a source result set to a target table based on a condition that we specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record.

Syntax of the Merge statement

MERGE targetTable Using sourceTable ON mergeCondition WHEN MATCHED THEN updateStatement WHEN MATCHED and othercondtion --optional THEN updateStatement  WHEN NOT MATCHED BY TARGET THEN insertStatement WHEN NOT MATCHED BY SOURCE THEN deleteStatement

The merge statement works using two tables, the source Table and target Table.  The target Table is the table to be modified based in data contained within the source Table.

There will be three conditions

• MATCHED(Source & Target)
• NOT MATCHED BY TARGET
• NOT MATCHED BY SOURCE

MATCHED – these are rows satisfying the match condition.  They are common to both the source and target tables. 

NOT MATCHED – This is also known as NOT MATCHED BY TARGET; these are rows from the source table that didn’t match any rows in the target table. 

NOT MATCHED BY SOURCE – these are rows in the target table that were never matched by a source record.

See the example:

Here I am having two tables known as EMP_Source and EMP_TARGET

Checking EMP_ID and EMP_NAME

If a match and EMP_ADD is not same then update EMP_ADD records in Target table.

If not match by the target, in this case, insert a record in Target table.

EMP_ID 2 address will be updated and set it to ‘PUNE’.

MERGE Emp_Target T Using Emp_Source S ON T.EMP_ID = S.EMP_ID and T.EMP_NAME=S.EMP_NAME WHEN MATCHED and T.EMP_ADD != S.EMP_ADD THEN UPDATE SET T.EMP_ADD=S.EMP_ADD WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (S.EMP_ID,S.EMP_NAME,S.EMP_ADD);

Running this script.

  

See the result

Target has been updated from ‘Patna’ to ‘Pune’.