MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008 which is really underutilized, I have seen many time recently that developers are still using separate DML statement for Insert / Update and Delete where there 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 is going to read data and process it in one shot rather than performing single statement to table each time you write.

I will give you one small example so that you can see how one can use MERGE statement or which situation we can use MERGE statement in???

Suppose we have one Member’s personal Detail table where we can find Memberid, member name, registration date and expiration date. There is one more table there for Member’s user name and password.

Now, we want to delete those users from memberLogin table whose expiration date has been met, we want to set default password for those member who are not expired right now and we want to make entry of those user who are just registered and id/password is not set yet.

--create Member's personal detail table and insert data in it.

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

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'

GO

SELECT * FROM MemberPersonalDetail

go

--create Member's login detail table and insert data in it.

CREATE TABLE MemberLoginDetail

(

MemberID INT,

UserName varchar(20),

UserPassword varchar(20)

)

GO

INSERT INTO MemberLoginDetail

SELECT 1,'Ritesh Shah','TestPassword' UNION ALL

SELECT 2,'Rajan Shah','goodluck'

GO

SELECT * FROM MemberLoginDetail

go

--MERGE statement with Insert / Update / Delete.....

--if you just need Insert / update or Insert / delete or Update / Delete anyting

-- you can use any combo

-- I have explained all three DML in one MERGE statement to demonstrate it.

MERGE MemberLoginDetail AS mld

USING (SELECT MemberID,MemberName,ExpirationDate FROM MemberPersonalDetail) AS mpd

ON mld.MemberID = mpd.MemberID

WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE

WHEN MATCHED THEN UPDATE SET mld.UserPassword = 'DefaultPassword'

WHEN NOT MATCHED THEN

INSERT(MemberID,UserName,UserPassword)

VALUES(mpd.memberID,mpd.MemberName,'DefaultPassword');

GO

--check the table whether operation is successfully done or not.

SELECT * FROM MemberLoginDetail

go

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”