SQL Merge

SQL MERGE allows you to perform multiple INSERT, UPDATE, and DELETE operations in a single statement. It is new in SQL server 2008. Based on selection criteria specified in the MERGE statement, you can conditionally apply INSERT, UPDATE, and DELETE statements to the table.

For example, you may want to UPDATE a row if it already existed and INSERT the row if it is not already in the table. You might also want to synchronize the contents of two tables.

The general syntax for MERGE is:

[Source of above syntax: MS SQL Server 2008 Step by Step by Mike Hotek]

Example of SQL Merge:
We have 2 tables viz. Employee and EmployeeGrades. Employee is having 2 columns i.e. ID and Name. And EmployeeGrades also have 2 columns i.e. ID (reference with Employee table column ID) and Grades.

Creation & Insertion Includes:

1. Create Employee table

CREATE TABLE Employee
(
ID INTEGER PRIMARY KEY,
Name VARCHAR(15)
)

2. Insert records in Employee

INSERT INTO Employee
VALUES(1,'AMIT')
INSERT INTO Employee
VALUES(2,'SURESH')
INSERT INTO Employee
VALUES(3,'KAPIL')
INSERT INTO Employee
VALUES(4,'SACHIN')
INSERT INTO Employee
VALUES(5,'LOKESH')

3. Show Records

SELECT * FROM Employee

Result:

ID	Name
1	AMIT
2	SURESH
3	KAPIL
4	SACHIN
5	LOKESH

4. Create EmployeeGrades Table

CREATE TABLE EmployeeGrades
(
ID INTEGER REFERENCES Employee,
Grades INTEGER
)

Insert records in EmployeeGrades

INSERT INTO EmployeeGrades
VALUES(1,130)
INSERT INTO EmployeeGrades
VALUES(2,155)
INSERT INTO EmployeeGrades
VALUES(3,100)

5. Show Records

SELECT * FROM EmployeeGrades

Result:

ID	GRADES
1	130
2	155
3	100

Query :Delete the records whose Grades are more than 150.
Update Grades and add 25 to each as internals if records exist.
Insert the records if record does not exists.

MERGE EmployeeGrades AS empg
USING (SELECT ID,Name FROM Employee) AS e
ON empg.ID = e.ID
WHEN MATCHED AND empg.Grades > 150 THEN DELETE
WHEN MATCHED THEN UPDATE SET empg.Grades = empg.Grades + 25
WHEN NOT MATCHED THEN
INSERT(ID,Grades)
VALUES(e.ID,25);

SELECT * FROM Employee
Result:

ID	Name
1	AMIT
2	SURESH
3	KAPIL
4	SACHIN
5	LOKESH

SELECT * FROM EmployeeGrades
Result:

ID	GRADES
1	155
4	25
3	125
5	25

Few points to consider while using SQL Merge:

1. Semicolon is mandatory after the merge statement.
2. When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.
3. MERGE statement also improves the performance of database as it passes through data only once.