KishoR NaiK: MS-SQL - Merge Statement in MS SQL Server

MERGE statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data.

The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a Source record set and a Target table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

In short we can say that we need to sync with tables with latest information.

In the below example we are updating the data from source table data to target table. For this we have written MERGE statement, that is performing INSERT, UPDATE & DELETE with a single MERGE statement

MERGE statement performing below operations

Insert

If EmployeeID not matched in target table inserting row from source table to target table

Update

If EmployeeID matched then updating first name,last name and city in target table from source table.

Delete

If EmployeeID in target table not matched with source table, deleting row from target table

Important points to remember while using the MERGE statement are:

The merge statement must be terminated by a semicolon otherwise an error will occur.

When there is a MATCH clause used along with some condition, it must be specified first amongst all other WHEN MATCH clauses.

Let see how to use Merge statement in SQL Server.

Step 1

First Create Source Table with sample Data it is look like this

-- Create Source Employee Table
CREATE TABLE SourceEmployee
(
 EmployeeID NUmeric(18,0) IDENTITY(1,1) PRIMARY KEY,
 FirstName Nvarchar(50),
 LastName Nvarchar(50),
 City Nvarchar(50)
)
-- Insert Record in Source Employee
INSERT INTO SourceEmployee
(FirstName,LastName,City)
VALUES
('Ashok','Patil','Mumbai')

INSERT INTO SourceEmployee
(FirstName,LastName,City)
VALUES
('Ajay','Patil','Mumbai')

INSERT INTO SourceEmployee
(FirstName,LastName,City)
VALUES
('Sam','Patil','Mumbai')

SELECT * FROM SourceEmployee

Click on Image for better view

Step 2
Now Create Target Table with sample Data it is look like this

-- Create Target Employee Table 
CREATE TABLE TargetEmployee
(
 EmployeeID NUmeric(18,0) IDENTITY(1,1) PRIMARY KEY,
 FirstName Nvarchar(50),
 LastName Nvarchar(50),
 City Nvarchar(50)
)

-- Insert Record in Target Employee

INSERT INTO TargetEmployee
(FirstName,LastName,City)
VALUES
('Ashok','Patil','Mumbai')

INSERT INTO TargetEmployee
(FirstName,LastName,City)
VALUES
('Ajay','Patil','Mumbai')

SELECT * FROM TargetEmployee

Click on Image for better view

Step 3
Now Use Merge Statement,it is look like this

-- Sync table using Merge statement
MERGE TargetEmployee as Target
USING SourceEmployee as Source
ON Target.EmployeeID=Source.EmployeeID
WHEN MATCHED THEN
 UPDATE 
  SET Target.FirstName=Source.FirstName,
   Target.LastName=Source.LastName,
   Target.City=source.City
WHEN NOT MATCHED BY TARGET THEN
  INSERT (FirstName,Lastname,City)
  VALUES (Source.FirstName,Source.LastName,Source.City)
WHEN NOT MATCHED BY SOURCE THEN
DELETE

OUTPUT $action,Inserted.*,Deleted.*;

Output