MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008which is really underutilized, I have seen many time recently that developersare still using separate DML statement for Insert / Update and Delete wherethere is a chance they can use MERGE statement of they can use condition based Insert/ Update and Delete in one shot. 
This will give performance advantage as complete process isgoing to read data and process it in one shot rather than performing singlestatement to table each time you write.
I will give you one small example so that you can see howone can use MERGE statement or which situation we can use MERGE statement in???
Suppose we have one Member’s personal Detail table where wecan find Memberid, member name, registration date and expiration date. There isone more table there for Member’s user name and password.
Now, we want to delete those users from memberLogin tablewhose expiration date has been met, we want to set default password for thosemember who are not expired right now and we want to make entry of those userwho are just registered and id/password is not set yet.
–createMember’s personal detail table and insert data in it.
Create Table MemberPersonalDetail
MemberID INT Identity(1,1),
MemberName Varchar(20),
RegisterDate date,
ExpirationDate date
INSERT INTO MemberPersonalDetail
SELECT ‘Ritesh Shah’,’01/01/2000′,’12/31/2015′ Union ALL
SELECT ‘Rajan Shah’,’02/07/2005′,’06/20/2011′ Union ALL
SELECT ‘Teerth Shah’,’06/22/2011′,’12/31/2015′
SELECT * FROMMemberPersonalDetail
–createMember’s login detail table and insert data in it.
CREATE TABLE MemberLoginDetail
MemberID INT,
UserName varchar(20),
UserPassword varchar(20)
INSERT INTO MemberLoginDetail
SELECT 1,‘Ritesh Shah’,‘TestPassword’ UNION ALL
SELECT 2,‘Rajan Shah’,‘goodluck’
SELECT * FROMMemberLoginDetail
–MERGEstatement with Insert / Update / Delete…..
–if you justneed Insert / update or Insert / delete or Update / Delete anyting
— you can useany combo
— I haveexplained all three DML in one MERGE statement to demonstrate it.
MERGEMemberLoginDetail AS mld
USING (SELECT MemberID,MemberName,ExpirationDateFROM MemberPersonalDetail) AS mpd
ON mld.MemberID = mpd.MemberID
WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE
WHEN MATCHED THEN UPDATE SET mld.UserPassword = ‘DefaultPassword’
–check thetable whether operation is successfully done or not.
SELECT * FROMMemberLoginDetail
Reference: Ritesh Shah
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: