MERGE Statement in SQL Server to Insert, Update, Delete Records

Introduction

In a single transaction (MERGE Statement) we can perform Insert, Update and Delete records on a existing table based on the result comparison between important columns with another table. Which also means we no longer needs to write multiple T-SQL statements to handle Insert, Update, Delete.

MERGE Statements

To perform MERGE, we need two tables as,

• Source Table – This table holds the changes that needs to applied to the target table.
• Target Table – The table that requires changes, that is in this table we will perform Insert, Update and Delete.

How the MERGE works

In simple term, MERGE statement JOINS the TARGET Table to the SOURCE Table with a common column in both tables. Based on the Rows that matches, it performs Insert, Update, Delete on the TARGET Table.

Syntax

MERGE [TARGET TABLE] AS T 
USING [SOURCE TABLE] AS S 
ON [JOIN CONDITIONS]
WHEN MATCHED THEN 
	[UPDATE STATEMENT]
WHEN NOT MATCHED BY TARGET THEN 
	[INSERT STATEMENT]
WHEN NOT MATCHED BY SOURCE THEN 
	[DELETE STATEMENT];

Example

For our example, lets take Details.SalesProducts as Target table and Details.Products as Source table.

SELECT * FROM Details.SalesProducts AS [Target Table]

SELECT * FROM Details.Products AS [Source Table]

Details.SalesProducts is the table where we are going to perform our MERGE Statement. From both the tables, the common column is ProductId which we will use it in joining the tables. When the rows are matched, then we need to perform Update Operation. When the rows are only present in the source table, then Insert Operation is performed. And when the rows are not present in the source table and present in target table, then the Delete Operation is performed.

Note: The MERGE statement should end with Semi Colon(;) always.

MERGE Details.SalesProducts AS T --- Target 
	USING Details.Products AS S --- Source 
	ON(T.ProductId=S.ProductId)
	WHEN MATCHED AND 
			T.ProductName<>T.ProductName OR 
			T.Rate<>S.Rate
	THEN UPDATE SET 
			T.ProductName=S.ProductName, 
			T.Rate=S.Rate
	WHEN NOT MATCHED BY TARGET THEN 
	INSERT 
	(
		ProductId,
		ProductName,
		Rate
	)
	VALUES
	(
		S.ProductId,
		S.ProductName,
		S.Rate
	)
	WHEN NOT MATCHED BY SOURCE 
	THEN DELETE;

Now after running the above MERGE statement, we can see the records in the both tables are same.

We can also wrap this MERGE statements into a Stored Procedure and execute it whenever its required.

Conclusion

In this article, We have discussed about the MERGE statements in SQL Server as simple as could. I hope you all found this article much useful. Please share your feedbacks in the comment section.