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.

create table Book
(
 BID int identity(1,1)primary key,
 Title varchar(50),
 Quantity int,
 Price int
)

Step 2: Create a source table.

create table BookOrder
(
 OID int identity(1,1)primary key,
 Title varchar(50),
 Quantity int,
 Price int
)

Step 3: Insert demo data into target table

insert Book values('ASP.Net 4.0',5,890)
insert Book values('C#.Net 4.0',8,990)
insert Book values('ADO.Net 4.0',7,400)
insert Book values('VB.Net 4.0',12,720)
insert Book values('Silverlight',4,880)

Step 4: Insert demo data into source table

insert into BookOrder values('C#.Net 4.0',4,990)
insert into BookOrder values('MVC4',4,1800)
insert into BookOrder values('ADO.Net 4.0',4,400)
insert into BookOrder values('JQuery',4,640)
insert into BookOrder values('WCF',3,740)

Step 5: Start operation using merge statement.

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

merge Book b using BookOrder o
on b.Title=o.Title
when matched then
update
 set b.Quantity=b.Quantity+o.Quantity;
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,

merge Book b using BookOrder o
on b.Title=o.Title
when matched then
update
 set b.Quantity=b.Quantity+o.Quantity
when not matched by target then
 insert (Title,Quantity,Price) values(o.Title,o.Quantity,o.Price);
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,

merge Book b using BookOrder o
on b.Title=o.Title
when matched then
update
 set b.Quantity=b.Quantity+o.Quantity
when not matched by source then
 delete;
 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.