Merge statement in SQL SERVER 2008
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...............