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.
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.
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];
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.
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.
Leave a Reply