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.

12 Comments

Join the conversation! Your thoughts help the community grow.

Bhuvan Pandey10y ago
Thanx every one to follow my article.
Subash10y ago
Good Very Nice
Nigel Fernandes10y ago
Useful
Raja T10y ago
Nice....
Mahesh Chand10y ago
Is this just SQL Server 2008 or any SQL? Or all SQL Server versions? Please clarify in the article. Most of the SQL commands usually applies to most of the common databases.
- Mahesh Chand10y ago
  Good point, Bhuvan. But when you write an article, doing that little extra research makes it more widely accepted. Title could be "Merge Statement In SQL Server" and note could say, SQL Server 2008 or higher or whatever the version this feature was released. If it this statement is a part of "SQL" language, that has to be clearly talked as well. Cheers!
- Bhuvan Pandey10y ago
  Sorry. I am not able to answer because I used to work only SQL Server 2008. I checked with some of article that marge command is same with SQL Server 2008, 2008 R2 and 2012.
Rakesh10y ago
Good share
Lalit Raghav10y ago
Nice article
- Bhuvan Pandey10y ago
  Thanks.
kalu singh rao10y ago
Good explanations
- Bhuvan Pandey10y ago
  Thanks.