Using MERGE in SQL Server to Insert, Update and Delete in Single Statement

Introduction: In this article I am going to explain what is MERGE Statement in SQL and How to perform insert, update and delete operation at same time in single statement using MERGE. 

In previous article i explained Sql Query to search any text in all stored procedures,views and functions and Get created or modified date of tables, stored procedures, views and functions and Using case statement inside IN clause (alternative) and Auto generate auto incremented alphanumeric sequential code in sql server and Convert table data to xml format using for xml path()

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.


Example:
Let’s assume our requirement is to insert update and delete employee salary when some condition matched.

Create Employee Table as:

CREATE TABLE Employee
(
   EmployeeID       INT PRIMARY KEY,
   EmployeeName VARCHAR(15)
)

Insert some dummy data into this table.

GO
INSERT INTO Employee
VALUES(1,'ANIL KUMAR')
INSERT INTO Employee
VALUES(2,'MOHAN')
INSERT INTO Employee
VALUES(3,'AJAY')
INSERT INTO Employee
VALUES(4,'VIKAS')
INSERT INTO Employee
VALUES(5,'RAVI')
GO

View table data:
SELECT * FROM Employee

Result will be as:
EmployeeID
EmployeeName
1
ANIL KUMAR
2
MOHAN
3
AJAY
4
VIKAS
5
RAVI

Now create a Salary Details table as

CREATE TABLE SalaryDetails
(
   EmployeeID        INT REFERENCES Employee,
   EmployeeSalary  INT
)

Insert some dummy data into this table.

GO
INSERT INTO SalaryDetails
VALUES(1,35000)
INSERT INTO SalaryDetails
VALUES(2,25000)
INSERT INTO SalaryDetails
VALUES(3,50000)
GO

View table data:
SELECT * FROM SalaryDetails

Result will be as:
EmployeeID
EmployeeSalary
1
35000
2
25000
3
50000

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

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..