Merge Command In Sql Server

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:-

 

Create table Import_Employee (Empno int, firstname nvarchar(100), lastname nvarchar(100))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.

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Merge Command, SQL Server and tagged , . Bookmark the permalink.

4 Responses to Merge Command In Sql Server

  1. Pratyasini says:

    realy nice post

  2. Nice article for Merge Command. It is really good.

  3. Good Example

Leave a Reply