Uses of Merge Statement in SQL Server

With the Merge statement we can merge data from a souce table into a target table.

Synatx

MERGE INTO <Target Table>: This cluase defines the target table for the operation.

USING <Source Table>: This clause defines the source table for the opearation. In Source Table we can use a table, CTE, Dervied Table, some other database table, OPENROWSET or XQUERY.

ON<Merge Condition>: This is just like an ON clause like in joins. This statement defines both the souce table and the target table as Matched or NotMatched.

WHEN MATCHED [AND Condition] THEN <ACTION>: This clause defines the When of both the Souce Table and the Target Table matched based on the key. Here [AND Condition] is optional. Here we can perform two actions, either update or delete on the target table.

WHEN NOT MATCHED [BY TARGET ] [AND Condition] THEN <ACTION>: This clause defines the When Target Table that is matched based on key. Here the [AND Condition] is optional. Here we can perform only the one action, Insert.

WHEN NOT MATCHED BY SOURCE [AND Condition] THEN <ACTION>: This clause defines the When Source Table that is matched based on key. Here the [AND Condition] is optional. Here we can perform one of two actions, either Update or Delete on the target table.

Realistic Scenario Using Merge Statement