Introduction to the MERGE Statement

Introduction to MERGE Statement

MERGE statement can perform INSERT UPDATE DELETE operations to the main target table when source table provided.To Perform a MERGE operation first of all you will need a Target and Source Table. For Example consider you have Student Table which contain students and in the next year that table data is going to be changed so you have to perform all the Data Modifying Queries on that table.

Lets Take Source table as follows

StudentID	Name
1	Amila
2	Nadun N
3	Roy

Target Table as Follows

StudentID	Name
1	Amila
2	Nadun
4	Rex

Consider that comparing target table to source table we can see following changes

StudentID 2 the name of that student has been changed to 'Nadun' to 'Nadun N'. (UPDATE)
StudentID number 3 has been Removed. (DELETE)
StudentID 4 has been newly added. (INSERT)

So in the above operation we have all the Data Modification Queries to execute to perform that we can use merge statement. Shown as below

Understanding the syntax

MERGE INTO Target table

This clause defines the target table for the operation. You can alias the table in this clause if you want.
USING source table
This clause defines the source table for the operation. You can alias the table in this clause if you want. Note that the USING clause is designed similar to a FROM clause in a SELECT query, meaning that in this clause you can define table operators like joins, refer to a table expression like a derived table or a common table expression (CTE), or even refer to a table function like OPENROWSET. The outcome of the USING clause is eventually a table result, and that table is considered the source of the merge operation.

ON merge predicate

In this clause, you specify a predicate that matches rows be tween the source and the target and defines whether a source row is or isn’t matched by a target row. Note that this clause isn’t a filter like the ON clause in a join.

WHEN MATCHED [AND predicate] THEN action

This clause defines an ac tion to take when a source row is matched by a target row. Because a target row exists, an INSERT action isn’t allowed in this clause. The two actions that are enabled are UPDATE and DELETE. If you want to apply different actions in different conditions, you can specify two WHEN MATCHED clauses, each with a different additional predicate to determine when to apply an UPDATE and when to apply a DELETE.

WHEN NOT MATCHED [BY TARGET] [AND predicate] THEN action

This clause defines what action to take when a source row is not matched by a target row. Because a target row does not exist, the only action allowed in this clause (if you choose to include this clause in the statement) is INSERT. Using UPDATE or DELETE holds no meaning when a target row doesn’t exist. You can still add an additional predicate that must be true in order to perform the action.

WHEN NOT MATCHED BY SOURCE [AND predicate] THEN action

This clause is a proprietary extension by Microsoft to the standard MERGE statement syntax. It defines an action to take when a target row exists, but it is not matched by a source row. Because a target row exists, you can apply either an UPDATE or a DELETE, but not an INSERT. If you want, you can have two such clauses with different additional predicates that define when to use an UPDATE and when to use a DELETE.

For our Above example we can write the corresponding merge statement as follows.
this will gives the output like this

StudentID	Name
1	Amila
2	Nadun N
3	Roy

Introduction to the MERGE Statement