One Statement for Insert, Update, Delete operations in SQL Server (Merge)

Introduction

       In this article, we will explain to perform Insert, Update, and Delete operations on Target table by matching the records from Source table by using a Single Statement in SQL Server.

Solution

      Generally we write separate statements to Insert, Update and Delete data based on certain conditions in SQL Server. But Merge statement using which we can do three operations in one statement. Simply says to perform multiple DML operations in one statement depends on some conditions. It is similar to the UPSERT command in Oracle.

Let’s create Employee1 and Employee2 details and inserted some records.

• Employee1 Details

create table Employee1

(Eid int,

Ename varchar(50),

Sal float)

insert into Employee1 values(1,'Ken J',15000)

insert into Employee1 values(2,'Terri Lee',10000)

insert into Employee1 values(3,'Rob M',25000)

insert into Employee1 values(4,'Diane L', 110000)

• Employee2 Details

create table Employee2

(Eid int,

Ename varchar(50),

Sal float)

insert into Employee2 values(1,'Ken J',15000)

insert into Employee2 values(3,'Rob M',25000)

insert into Employee2 values(5,'Janice M',12000)

insert into Employee2 values(2,'Terri Lee',33330)

insert into Employee2 values(4,'Diane L',20000)

• The data in two tables are shown below

Eid	Ename	Sal
1	Ken J	15000
2	Terri Lee	10000
3	Rob M	25000
4	Diane L	20000

Eid	Ename	Sal
1	Ken J	15000
2	Terri Lee	33330
3	Rob M	25000
4	Diane L	110000
5	Janice M	12000

In our example we will consider the main conditions are shown below

• Delete the records whose salary is greater than 100000.

• Update salary of Target table if the records exist in Source table and get the salary from Source table.

• Insert the records if record does not exist in Target table.

Now we will write the Merge statement to satisfied the above three conditions.

Merge into Employee1 T

using Employee2 S

on T.eid=S.eid

when matched and T.sal>100000 then Delete

when matched then update set T.sal=S.sal

when not matched then

insert (eid,ename,sal) values (S.eid,S.ename,S.sal);

Semicolon is mandatory after the Merge Statement.

• After Execute the Merge Statement

Final Output is

Eid	Ename	Sal
1	Ken J	15000
2	Terri Lee	33330
3	Rob M	25000
5	Janice M	12000

• Explanation is Merge Statement simply explain diagrammatically as follows.

Let me know what you think about this article.