Merge Statement in SQLServer

The use of the MERGE Statement in SQLServer

 

The Merge statement was introduced in SQLServer 2008 and allows us to do inserts, updates and deletes in one statement. This means we do not need to use multiple statements for doing inserts, updates and deletes.

 

The Merge statement requires two tables as in the following:

1. Source Table: The table containing the changes that need to 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 of the 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 table to join them.

 

 

1. Now when the rows match between the tables we want to do an update on 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.
   
2. Now when rows do not match then that means that for rows present in the Source table (and not in the Target table) we want to insert into the Target table. In the example the Bhumika record with ID equal to 2 is only present in the EmployeeSource table (it is not present in the EmployeeTarget table) so we want to insert this record into the Target table.
   
3. Finally, rows that are 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 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. It will be inserted into the EmployeeTarget table. The Sourabh record is only in the EmployeeTarget table but not in the EmployeeSource table so it will be deleted from the EmployeeTarget table.

 

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

 

 

Now let's understand it with a practical implementation.

 

Step 1

 

First we will open SQLServer Management Studio and then we will create tables by using the following queries.

Create the first table EmployeeSource as in the following:

1. create table EmployeeSource  
2. (  
3.    ID int primary key,  
4.    Name nvarchar(20)  
5. )  
6. GO  

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

Create the second table EmployeeTarget as in the following:

1. create table EmployeeTarget  
2. (  
3.    ID int primary key,  
4.    Name nvarchar(20)  
5. )  
6. GO  

1. Insert into EmployeeTarget values(1,'Shaili D')  
2. Insert into EmployeeTarget values(3,'Sourabh')  
3. 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. 

1. MERGE INTO EmployeeTarget AS T  
2. USING EmployeeSource AS S  
3. ON T.ID = S.ID  
4. WHEN MATCHED THEN  
5. UPDATE SET T.NAME=S.NAME  
6. WHEN NOT MATCHED BY TARGET THEN  
7. INSERT (ID,NAME)VALUES(S.ID,S.NAME)  
8. WHEN NOT MATCHED BY SOURCE THEN  
9. DELETE;  

Now we will see that in the output, the Target table has been updated. The Shaili record is updated, the Bhumika record is inserted and the Sourabh record that wass 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 remenber is that a MERGE statement must end with a semicolon (;), otherwise it throws an error.

 

Step 3

 

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

 

 

So we can do only two statements or we can ommit the last delete statement so when we execute the query after a delete the last delete statement in the output (the sourabh record 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.

1. Truncate table EmployeeSource  
2. Truncate table EmployeeTarget  
3. GO  

1. Insert into EmployeeSource values(1,'Shaili')  
2. Insert into EmployeeSource values(2,'Bhumika')  
3. Go  
4.   
5. Insert into EmployeeTarget values(1,'Shaili D')  
6. Insert into EmployeeTarget values(3,'Sourabh')  
7. GO  

Now see the output of both tables like this.

 

 

 

Step 4

 

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

1. MERGE INTO EmployeeTarget AS T  
2. USING EmployeeSource AS S  
3. ON T.ID = S.ID  
4. WHEN MATCHED THEN  
5. UPDATE SET T.NAME=S.NAME  
6. WHEN NOT MATCHED BY TARGET THEN  
7. INSERT (ID,NAME)VALUES(S.ID,S.NAME);  

So now the output will be like this.

 

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