MERGE statement in SQL Server 2008 and later version

Wednesday, June 29, 2011 |


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

0 comments: