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. 

• Tweet
• Share
• Share
• Share

Related Post