Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations.
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Consider there are two tables StudentDetails and StudentTotalMarks


In our example we will consider three main conditions while we merge this two tables.
1. Delete the records whose marks are more than 250.
2. Update marks and add 25 to each as internals if records exist.
3. Insert the records if record does not exists
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO



There are two very important points to remember while using MERGE statement.
• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.