Merge Command:- Merge command is a new feature introduced in the SQL Server 2008. It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.) It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.
The Syntax of the Merge command is given below:-
MERGE [AS TARGET]
USING [AS SOURCE]
ON
[WHEN MATCHED
THEN ]
[WHEN NOT MATCHED [BY TARGET]
THEN ]
[WHEN NOT MATCHED BY SOURCE
THEN ];
For example, suppose we have two tables in which Import_Employee is a source table and Employee is a target table.
The query for creating the Source table Import_Employee and inserting its data is given below:-
Insert into Import_Employee (Empno, firstname, lastname)
select 1,'Vivek', 'Johari'
Union All
select 2,'Ravi', 'Kumar'
Union All
select 3,'Uma', 'Sharma'
Union All
select 4,'Neha', 'Sharma'
Select * from Import_Employee
The query for creating the Target table Employee and inserting its data is given below:-
create table Employee(Empno int, fname nvarchar(100), lname nvarchar(100),enddate datetime)
Insert into Employee (Empno, fname, lname)
select 3,'Jagdish', 'Johari'
Union All
select 4,'Neha', 'Sharma'
Union All
select 5,'Sunny', 'Rana'
Select * from Employee
Suppose we have the following requirements:-
1) If a empno is exists in both the table then the corresponding values of the column Fname and Lname for the empno in the table Employee is updated by the corresponding values of the column firstname and lastname of the Empno from the source table Import_Employee .
2) If there is no corresponding empno in the Target table Employee for a empno of the table Import_Employee, then the data from the Source table is inserted into the target table for that empno.
3) If there exists a records for a empno in the Target table whose corresponding records are not in the Source table, these these record should be deleted from the target table.
Therefore, the condition on which the target table and Source table is joined will be the Empno.
The merged statement fulfilling all the above three requirements are given below:-
MERGE employee AS TARGET
USING Import_Employee AS SOURCE
ON TARGET.empno=SOURCE.empno
WHEN MATCHED
THEN update set TARGET.fname=SOURCE.firstname,
TARGET.lname = SOURCE.lastname
WHEN NOT MATCHED BY TARGET THEN
insert(empno,fname,lname)
values(SOURCE.empno,SOURCE.firstname,SOURCE.lastname)
WHEN NOT MATCHED BY SOURCE THEN
Delete;
Select * from Employee 
In the above example, the target table "Employee" and the source table "Import_Employee " has the same empno 3, so the fname and the lname of the table employee is updated with the corresponding firstname and lastname column value from the table Import_Employee for empno 3.
Also the table employee does not have the record for the empno 1 and 2. Therefore the corresponding record for the empno 1 and 2 will be inserted from Import_Employee (source) table to the Employee(Target) table.
Again, the target table employee contains the records for the empno 5 whereas the source table Import_Employee do not contains any records for empno 5 , therefore the record for empno 5 is deleted from the target table by this Merge statement.
So we can see from the above example that with the help of Merge statement, we can perform the update, delete and insert commands within a single statement.
