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     2691
  Rating     5
Author Najmul Hoda   Posted On     17 Jun 2011
Tags SQL SERVER,General    

Sample Code   Download Code




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.


    [ 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
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



MERGE STATEMENT is being executed here


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


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



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

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


About Author

About Author He is a team lead, having 8+ years of experience. He loves writing technical article for .Net developers. Najmul Hoda
No Photo
Country United States
Company BirlaSoft
Home Page

Rate this article

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


Write your comment here.
Verification Code