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.


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

  • 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
    select * from @source
)   as b on =
when matched and<> then update set =, a.status = 'updated'
when not matched by target then insert (id, name, status) values (,, 'inserted')
when not matched by source then delete;

select * from @target


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

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s