User Id :    Password :      New Member   Forgot Password
 
Merge statement in SQL SERVER 2008
Description Merge is a great feature introduced in SQL server 2008 to perform multiple DML operations like INSERT, UPDATE and DELETE in a single statement very efficiently.   No. of Views     2085
  Rating     5
Author Najmul Hoda   Posted On     17 Jun 2011
Tags SQL SERVER,General    

Sample Code   Download Code

 

Introduction

 

Merge is a great feature introduced in SQL server 2008 to perform multiple DML operations like INSERT, UPDATE and DELETE in a single statement very efficiently.

 

Before Merge statement was inroduced, we had had to write 3 different statements to perform INSERT , UPDATE and DELETE like operations.

 

Using a sungle statement we can add records if there is NO MATCH, we can add check for deleting and updating records if there is MATCH.

 

Here is the syntax for MERGE STATEMENT.

 

MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source>
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]

 

Lets take an example to describe MERGE STATEMENT.

 

We have 2 tables tblAdvertiement and tblAdvertiementDetail. Table tblAdvertizment keeps AdvertizementId and AdvertizerName. Table tblAdvertimentDetail stores AdvertiementId, ActivatatedOn and DeactivatedOn data.

 

We will do the folowing operations in executing MERGE statment.

1. Delete records from  tblAdvertimentDetail where DeactivatedOn is less than Today's date. ------ WHEN MATCHED [AND clause_search_condition]

2. Update DeactivatedOn by adding one MONTH in tblAdvertizementDetail where there is match ------ WHEN MATCHED

3. Insert those record in tblAdvertiementDetail from tblAdvertizement where AdvertizementId is not present in tblAdvertizementDetail table. ------ WHEN NOT MATCHED

 

Lets create these table.

.

CREATE TABLE tblAdvertizement
(
Id INT PRIMARY KEY,
AdvertizementName VARCHAR(20)
)

CREATE TABLE tblAdvertizementDetail
(
Id INT ,
ActivatedOn DATE,
DeActivatedOn DATE
)

 

Now insert data into these tables.

 

INSERT INTO tblAdvertizement(Id,AdvertizementName) SELECT 1,'HP'

INSERT INTO tblAdvertizement(Id,AdvertizementName) SELECT 2,'Dell'

INSERT INTO tblAdvertizement(Id,AdvertizementName) SELECT 3,'Nokia'

INSERT INTO tblAdvertizement(Id,AdvertizementName) SELECT 4,'Samsung'

INSERT INTO tblAdvertizement(Id,AdvertizementName) SELECT 5,'LG'

INSERT INTO tblAdvertizement(Id,AdvertizementName) SELECT 6,'SONY'

INSERT INTO tblAdvertizementDetail(Id,ActivatedOn,DeActivatedOn) SELECT 1,'01/01/2011','05/01/2011'

 

Data before Merge statement is executed.

 

SELECT 'Before Merge'

SELECT * FROM tblAdvertizement

SELECT * FROM tblAdvertizementDetail

GO

 

MERGE STATEMENT is being executed here

 

MERGE tblAdvertizementDetail
USING tblAdvertizement
ON tblAdvertizementDetail.Id=tblAdvertizement.Id
WHEN MATCHED AND tblAdvertizementDetail.DeActivatedOn<GETDATE() THEN
DELETE
WHEN MATCHED THEN
UPDATE SET DeactivatedOn=DATEADD(MONTH,1,DeactivatedOn)
WHEN NOT MATCHED THEN
INSERT VALUES(tblAdvertizement.Id,GETDATE(),DATEADD(MONTH,5,GETDATE()));
 

 

Data after MERGE statement is executed.

 

SELECT 'After Merge'
SELECT * FROM tblAdvertizementDetail

 

Now Drop table 2 table if not needed.

 

DROP TABLE tblAdvertizement

DROP TABLE tblAdvertizementDetail

 

OUT PUT


Hope u liked it................ :)

Happy reading...............


 

About Author

About Author He is a .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by