Tuesday, 9 June 2015

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. 

1 comment: