Uses of Merge Statement in SQL Server

Introduction

In this article, we will learn about the Uses of the Merge statement in SQL Server. Find a detailed article about the Merge statement in SQL Server here: Merge Statement in SQL Server.

Uses of MERGE Statement in SQL Server

The Merge statement was introduced in SQL Server 2008 and allowed us to do inserts, updates, and deletes in one statement. This means we do not need multiple statements for inserts, updates, and deletes.

The Merge statement requires two tables as in the following:

1. Source Table: The table containing the changes that must be applied to the target table. 
2. Target Table: The table that requires the changes (insert, update and delete).

The Merge statement joins the target table to the source table using a common column in both tables. Based on how the rows match the inserts, updates and deletes are done on the target table.

Let's understand with an example.

Here we have EmployeeSource and EmployeeTarget tables. The EmployeeTarget table is the table that will get the inserts, updates, and deletes all in one statement. We will use an ID column in both tables to join them.

Now, when the rows match between the tables, we want to update the EmployeeTarget table. So at the moment, in both tables, we have a record ID equal to 1. So those rows match, and we want to do an update. In other words, in the example, we want to update Shaili D to Shaili.

Now when rows do not match, that means that for rows present in the Source table (and not in the Target table), we want to insert them into the Target table. In the example, the Bhumika record with an ID equal to 2 is only present in the EmployeeSource table (not in the EmployeeTarget table), so we want to insert this record into the Target table.

Finally, rows only in the Target table (but not present in the Source table) are to be deleted (all such rows) from the Target table. At the movement, the Sourabh record with an ID equal to 3 is only present in the EmployeeTarget table but not present in the EmployeeSource table. So we want to delete it from the EmployeeTarget table.

Now, after execution of the MERGE statement, the output will be that the Shaili D record is updated to Shaili in the EmployeeTarget table and the Bhumika record with ID equal to 2 that is only present in the EmployeeSource table but not in the EmployeeTarget table. The Sourabh record is only in the EmployeeTarget table but not in the EmployeeSource table, so that it will be deleted from the EmployeeTarget table. It will be inserted into the EmployeeTarget table.

The following is the syntax to do it with the output.

Now let's understand it with practical implementation.

Step 1

First, we will open SQL Server Management Studio and then create tables using the following queries.

Create the first table EmployeeSource as in the following.

create table EmployeeSource  
(  
   ID int primary key,  
   Name nvarchar(20)  
)  
GO  

Insert into EmployeeSource values(1,'Shaili')  
Insert into EmployeeSource values(2,'Bhumika')  
Go

Create the second table EmployeeTarget as in the following:

create table EmployeeTarget  
(  
   ID int primary key,  
   Name nvarchar(20)  
)  
GO  

Insert into EmployeeTarget values(1,'Shaili D')  
Insert into EmployeeTarget values(3,'Sourabh')  
GO  

Now the tables look like this.

Step 2

After execution of the MERGE statement from the following query, we want the data in the Target table to be the same as the Source table. 

MERGE INTO EmployeeTarget AS T  
USING EmployeeSource AS S  
ON T.ID = S.ID  
WHEN MATCHED THEN  
UPDATE SET T.NAME=S.NAME  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (ID,NAME)VALUES(S.ID,S.NAME)  
WHEN NOT MATCHED BY SOURCE THEN  
DELETE;  

Now we will see that the Target table has been updated in the output. The Shaili record is updated, the Bhumika record is inserted, and the Sourabh record present only in the Target table is deleted from the target table.

So we have done everything, inserts, updates, and deletes in one MERGE statement.

One thing to remember is that a MERGE statement must end with a semicolon (;); otherwise it throws an error.

Step 3

In practice, we usually do only an update and insert but not a delete of the data present in the target table and not present in the source table. Usually, the source table provides new records that are added and any records that need to be updated.

So we can make only two statements, or we can commit the last delete statement so when we execute the query after deleting the previous delete statement in the output (the sourabh record is only present in the target table) is left in the target table.

So now, we will truncate the tables and insert the previous data using the following query.

Truncate table EmployeeSource  
Truncate table EmployeeTarget  
GO  

Insert into EmployeeSource values(1,'Shaili')  
Insert into EmployeeSource values(2,'Bhumika')  
Go  
  
Insert into EmployeeTarget values(1,'Shaili D')  
Insert into EmployeeTarget values(3,'Sourabh')  
GO  

Now see the output of both tables like this.

Step 4

Now we will delete the last statement from the MERGE query and execute the following query. 

MERGE INTO EmployeeTarget AS T  
USING EmployeeSource AS S  
ON T.ID = S.ID  
WHEN MATCHED THEN  
UPDATE SET T.NAME=S.NAME  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (ID,NAME)VALUES(S.ID,S.NAME); 

 So now the output will be like this.

Notice that the Shaili record is updated, the Bhumika record is inserted, and the Sourabh record is not deleted in the EmployeeTarget table.

Conclusion

This article taught us about the uses of Merge Statements in SQL Server. Find a detailed article about the Merge statement in SQL Server here: Merge Statement in SQL Server.