USING MERGE IN SQL SERVER TO INSERT, UPDATE AND DELETE IN SINGLE STATEMENT

Description:

Merge statement joins the target table to the source table by using a column common to both tables, such as a primary key. MERGE can be used to combine insert, update, and delete operations into one statement.

 MERGE: A single statement for insert, update and delete 

One of the most important advantage of MERGE statement is all the data is read and processed only once.

We can use the MERGE statement to:

·        Conditionally insert or update rows in a target table.

If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.

·       Synchronize two tables.

     We can synchronize two tables by inserting, updating, or deleting rows in target table based on differences found in the Source table.

Points to remember while using MERGE statement.

• Semicolon is mandatory after the merge statement, otherwise it will give error.
• MERGE SQL statement improves the performance as all the data is read and processed only once. Without using MERGE statement we need to write three different statements to perform (INSERT, UPDATE or DELETE) operation and data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause. 

Now my requirement is to Delete those employees records from SalaryDetails table whose salary is 50000 and also UPDATE all employee salary by 40000 when record matched and INSERT new record in table when record not matched.

MERGE SalaryDetails AS SD

USING (SELECT EmployeeID, EmployeeName FROM Employee) AS EM

ON SD.EmployeeID=EM.EmployeeID 

WHEN MATCHED AND SD.EmployeeSalary = 50000 THEN DELETE

WHEN MATCHED THEN UPDATE

SET SD.EmployeeSalary =SD.EmployeeSalary + 40000

WHEN NOT MATCHED THEN

INSERT(EmployeeID,EmployeeSalary) VALUES (EM.EmployeeID,12000);

Finally the result will be as:

EmployeeID	EmployeeSalary
1	75000
2	65000
4	12000
5	12000