MERGE feature was introduced by Microsoft in SQL Server 2008.

MERGE statement allows you to perform INSERT, UPDATE & DELETE command with a single statement, and so is very efficient way to perform multiple DML operations.

In earlier version of SQL Server, we had to write multiple DML statements for INSERT, UPDATE & DELETE.

Let's take an example to understand how MERGE statement works.

In the below example we are updating the data from source table data to target table.

For this we are going to write MERGE statement that would be perform INSERT, UPDATE & DELETE with a single MERGE statement

MERGE statement performing below operations:

  1. declare @source table( id int, name varchar(50))
  2. declare @target table( id int, name varchar(50), status varchar(10))
  4. insert into @source values (1, 'abc'), (2,'pqr' ), (3, 'xyz')
  5. insert into @target(id, name) values (1, 'abc'), (2,'sdfdf'), (4, 'abc')
  7. select * from @target
  9. merge @target as a
  10. using
  11. (
  12. select * from @source
  13. )
  14. as b on a.id = b.id
  15. when matched and a.name<>b.name then update set a.name = b.name, a.status = 'updated'
  16. when not matched by target then insert (id, name, status) values (b.id, b.name, 'inserted')
  17. when not matched by source then delete;
  19. select * from @target
Output

Output

Let's Compare the data of target table before and after merge statement.