Merge Statement In SQL Server 2008

What is Merge command?

Merge provides an efficient way to perform multiple DML operations. Using the MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched, then just update it, and when unmatched, then insert it.

Step by step explanation of merge statement:

Step 1: Create a target table.

  1. create table Book  
  2. (  
  3.  BID int identity(1,1)primary key,  
  4.  Title varchar(50),  
  5.  Quantity int,  
  6.  Price int  
  7. )  

Step 2: Create a source table.

  1. create table BookOrder  
  2. (  
  3.  OID int identity(1,1)primary key,  
  4.  Title varchar(50),  
  5.  Quantity int,  
  6.  Price int  
  7. )  
Step 3: Insert demo data into target table
  1. insert Book values('ASP.Net 4.0',5,890)  
  2. insert Book values('C#.Net 4.0',8,990)  
  3. insert Book values('ADO.Net 4.0',7,400)  
  4. insert Book values('VB.Net 4.0',12,720)  
  5. insert Book values('Silverlight',4,880)  
Step 4: Insert demo data into source table
  1. insert into BookOrder values('C#.Net 4.0',4,990)  
  2. insert into BookOrder values('MVC4',4,1800)  
  3. insert into BookOrder values('ADO.Net 4.0',4,400)  
  4. insert into BookOrder values('JQuery',4,640)  
  5. insert into BookOrder values('WCF',3,740)  
Step 5: Start operation using merge statement.

Example 1: When Matched: Update tagrate table from source data

  1. merge Book b using BookOrder o  
  2. on b.Title=o.Title  
  3. when matched then  
  4. update  
  5.  set b.Quantity=b.Quantity+o.Quantity;  
  6. Truncate table BookOrder  
Note: Truncate Source table after completion of the operation. In given code, Book table matches with BookOrder table on the basis of Title and if records are matched into both tables, only those records will update.

Example 2: When not Matched: Update tagrate table from source data if it matches otherwise insert new row from source table,

  1. merge Book b using BookOrder o  
  2. on b.Title=o.Title  
  3. when matched then  
  4. update  
  5.  set b.Quantity=b.Quantity+o.Quantity  
  6. when not matched by target then  
  7.  insert (Title,Quantity,Price) values(o.Title,o.Quantity,o.Price);   
  8. Truncate table BookOrder  
Note: Truncate Source table after completing all the operations. In given code, book table matches with BookOrder table on the basis of Title and if records are matched in both tables, only those records will update and other records will insert into table as a new row.  

Example 3: When not Matched: Update tagrate table from source data if  it matches otherwise delete row from Targate table,

  1. merge Book b using BookOrder o  
  2. on b.Title=o.Title  
  3. when matched then  
  4. update  
  5.  set b.Quantity=b.Quantity+o.Quantity  
  6. when not matched by source then  
  7.  delete;  
  8.  Truncate table BookOrder  
Note: Truncate Source table after completing all the operations. In given code, Book table matches with BookOrder table on the basis of Title and if records are matched into both tables, only those records will update and records will delete from source table if records are not matched into tables.