Post
Ask Question

Merge Feature By Microsoft in SQL Server 2008

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:

  • 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

 

  1. declare @source table( id int, name varchar(50))  
  2. declare @target table( id int, name varchar(50), status varchar(10))  
  3.   
  4. insert into @source values (1, 'abc'), (2,'pqr' ), (3, 'xyz')  
  5. insert into @target(id, name) values (1, 'abc'), (2,'sdfdf'), (4, 'abc')  
  6.   
  7. select * from @target  
  8.   
  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;  
  18.   
  19. select * from @target  
Output

Output

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

 

  • Name updated with id 2.
  • Row inserted with id 3.
  • Row deleted with id 4.