Merge Statement in SQL Server

Hi Guys,

Yesterday while while working with my current application I got a situation where I have to Insert/Update/Delete records from tables based on some conditions. After searching for the solution in google in came to know MERGE statement of SQL server which does all 3 task INSERT/UPDATE/DELETE at a time based on your condition and i found it very useful so sharing this with you.

Example:

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.

Conditions:

MERGE statement performing below operations

  • UPDATE  : If id matched and name not matched, updating name in target table from source table.
  • INSERT   : If id not matched in target table inserting row from source table to target table
  • DELETE : If id in target table not matched with source table, deleting row from target table

SQL Statement for MERGE:

declare @source table( id int, name varchar(50))
declare @target table( id int, name varchar(50), status varchar(10))

insert into @source
values (1, 'abc'), (2,'pqr' ), (3, 'xyz')
insert into @target(id, name)
values (1, 'abc'), (2,'sdfdf'), (4, 'abc')

select * from @target

merge @target as a
using 
(
    select * from @source
)   as b on a.id = b.id
when matched and a.name<>b.name then update set a.name = b.name, a.status = 'updated'
when not matched by target then insert (id, name, status) values (b.id, b.name, 'inserted')
when not matched by source then delete;

select * from @target

Output:


Isn't is good? I like it very useful.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s