Demystifying SQL MERGE

Introduction

In this article, we are going to see about Merge statement in sql server. Merge statement allows us to perform insert , update , delete operations in one statement without using multiple statements for each of these operations.

How to use merge statement

Merge statement compares source table with destination table by using common column or key between the two tables and perform insert , update or delete operations based on the conditions we specify for performing these operations.

For using merge statement , we need two tables

1. Source table – The table which contains the data source that is to be compared with the target table.
2. Target table – The table in which insert, update and delete operations to be performed.

Syntax

MERGE INTO <TARGET TABLE> as TARGET
USING <SOURCE TABLE> as Source
ON <MAPPING CONDITIONS>
WHEN MATCHED THEN 
[INSERT / UPDATE / DELETE OPERATION]
WHEN NOT MATCHED BY SOURCE 
[INSERT / UPDATE / DELETE OPERATION]
WHEN NOT MATCHED BY TARGET
[INSERT / UPDATE / DELETE OPERATION]

Example

Consider two tables SourceTable and TargetTable.

Now, we are going to copy the data that are stored in source table to target table based on the conditions. First we are comparing both the tables based on the Id column.

Now we are going to perform insert , update and delete operations on below scenarios.

1. If there are matching rows between these two tables, we are going to update those rows.

Merge into TargetTable as Target
Using SourceTable as Source
on Target.Id = Source.Id

//Perform Updates Operations when matched
When Matched then 
Update Target.Name = Source.Name,
       Target.Country = Source.Country

As we can see, there are two rows that are matched by Id column (ie.., Id 1 and 2). These rows are updated in target table. And the result obtained after performing this action will be,

2. If there are unmatched rows in source table, we are going to insert those data from source table to target table.

Merge into TargetTable as Target
Using SourceTable as Source
on Target.Id = Source.Id

//Perform Updates Operation when matched
When Matched then 
Update Target.Name = Source.Name,
       Target.Country = Source.Country

//Perform Insert Operation
When Not matched by Target then
       Insert (Id,Name,Country)
       Values(Source.Id,Source.Name,Source.Country)

As we can see, there are two rows that are not matched by target table (ie.., Id 3 and 4). So these data are inserted from source table to destination table. After executing this query, target table will looks like

3. If there are unmatched rows in target table, we are going to delete those data from target table.

Merge into TargetTable as Target
Using SourceTable as Source
on Target.Id = Source.Id

//Perform Updates Operation when matched
When Matched then 
Update Target.Name = Source.Name,
       Target.Country = Source.Country

//Perform Insert Operation
When Not matched by Target then
       Insert (Id,Name,Country)
       Values(Source.Id,Source.Name,Source.Country)

//Perform Delete Operation
When Not matched by Source then
       Delete;

As we can see, the unmatched row(s) in the target table (ie.., Id 5) is deleted. And after performing this operation, the data in the target table will be

Conclusion

Comparing to performing insert, update and delete operations individually, merge statement performs faster as the data is read only once. The above example clearly shows how merge statement work and how to use merge statement.