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. 

Simplest Method to delete duplicate records in a Table

Introduction

          In this article, I will explain how to delete duplicate records in a table by using SQL Query in a simple way.

Solution

• Create a table with the following structure

create table Emp_Data

(Eid int,

Ename varchar(50),

Sal float)

• Insert the values into the table

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

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

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

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

insert into Emp_Data values(6,'Kevin F',12500)

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

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

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

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

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

• The data in this table as shown below

Eid	Ename	Sal
1	Ken J	15000
3	Rob M	25000
4	Diane L	20000
5	Janice M	12000
6	Kevin F	12500
1	Ken J	15000
3	Rob M	25000
5	Janice M	12000
1	Ken J	15000
2	Terri Lee	10000

Employee name “Ken J” repeated 3 times, “Rob M” repeated 2 times and “Janice M” repeated 2 times.

• If you want to delete all the rows if the selected columns repeated more than 1 then in the simple way by using the following method.

with cte_Empdata as

(

select ROW_NUMBER() over(partition by Eid,Ename order by eid) as rowno,* from Emp_Data

)

delete from cte_Empdata where rowno>1

• After delete the duplicate records

Final Output is as follows

select * from Emp_Data

Eid	Ename	Sal
1	Ken J	15000
4	Diane L	20000
5	Janice M	12000
6	Kevin F	12500
3	Rob M	25000
2	Terri Lee	10000

Let me know what you think about this article. 

SQL Server Real Time Query

Problem

           Write a stored procedure with parameters to pick the data with that date. If there is not data in insurance project then it has to pick from IT otherwise Banking.

Solution

First to create three tables in three various projects

use insurance

create table insurance_data

(

empid int,

date1 date,

amt1 int)

empid	date1	amt1
100	01-01-2015	10
101	02-01-2015	100
102	03-01-2015	1000
103	04-01-2015	10000

use it

create table it_data

(

empid int,

date2 date,

amt2 int)

empid	date2	amt2
201	01-02-2015	20
202	02-02-2015	200
203	03-02-2015	2000
204	04-01-2015	20000

use banking

create table banking_data

(

empid int,

date3 date,

amt3 int)

empid	date3	amt3
301	01-03-2015	30
302	02-03-2015	300
303	03-03-2015	3000
304	04-02-2015	350

Stored procedure with parameters as follows

create procedure GetData_3Projects

(

@input_date date,

@output_val int output

)

as

begin

      set @output_val=0

      select @output_val=amt1 from insurance.dbo.insurance_data where                                                                             date1=@input_date

      if(@output_val = 0)

            select @output_val=amt2 from it.dbo.it_data where                                                                 date2=@input_date

      if(@output_val = 0)

            select @output_val=amt3 from banking.dbo.banking_data where                                                                 date3=@input_date

return

end

Output

declare @output int

exec GetData_3Projects '2015-02-04',@output output

print @output

Output is 250.

Let me know what you think about this article.