MERGE statement in SQL Server 2008

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.

Example :

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT 'Company','01/01/2000','12/31/2015' Union ALL

SELECT 'ItCompany','02/07/2005','06/20/2011' Union ALL

SELECT 'Marketing','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,'Company','TestPassword' UNION ALL

SELECT 2,'ItCompany','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