Wednesday, June 06, 2007

Fun with the new MERGE statement

I found some time to play with CTP3 and checked out the MERGE statement. What you achieve with it may not be rocket science and was perfectly possible with a couple of IF statements but using MERGE gives you much cleaner code. It looks like Microsoft is investing a lot in 'cleaner' coding in SQL Server 2008, which I can only welcome with great enthusiasm.

A simple example will show you exactly what the MERGE statement does.

CREATE TABLE StagedData
(ID int, UserName varchar(20))
GO

CREATE TABLE RealData
(ID int, UserName varchar(20))
GO

INSERT INTO StagedData VALUES (1, 'Slava') , (2, 'Paul') , (3, 'Wesley')
GO

MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

UPDATE StagedData SET UserName = 'Kimberley' WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

DELETE FROM StagedData WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

DROP TABLE StagedData
GO
DROP TABLE RealData
GO

5 comments:

thomarse said...

Nice example, thanks a lot.

WesleyB said...

Thanks for the comment, I'm glad that people let me know they find something useful on my blog ;-)

gayu said...

Informative blog.
Just one doubt - how do I redirect rows using MERGE. e.g. If there are any extra rows present in the source(NOT MATCHED BY SOURCE), I want to redirect them to a table called (say) Error, how do I go about implementing it using single MERGE?

gayu said...
This comment has been removed by the author.
WesleyB said...

gayu,

Sorry for the late reply. Between all the spam I get on my mail I did not see the notification :-)

I do not think this is possible as such. You should look into the OUTPUT statement on MERGE because you can achieve quite a lot with nesting the MERGE statement. But I do not think you can insert into another object in the same MERGE statement. If you feel that this would be a valuable addition to SQL Server please feel free to submit this on http://connect.microsoft.com (I think it would be a nice addition ;-p)