A Merge Code Sample and A Link for Issues


 
If exists(Select object_id('dbo.mytest','U')) 
drop table dbo.mytest;
If exists(Select object_id('dbo.mylog','U')) 
drop table dbo.mylog;

CREATE TABLE dbo.mytest(id INT);
CREATE TABLE dbo.myLog(action varchar(10), idnew int, idold int);
GO
INSERT dbo.mytest VALUES(1),(5);
GO

select * from mytest

--Check Requests Log
INSERT INTO  dbo.mylog (action,  idNew, idOld)
SELECT  action,   idNew, idOld
FROM
( 
MERGE dbo.mytest WITH (HOLDLOCK) AS Target
USING (VALUES(1),(2),(3)) AS Source(id)
ON Target.id = Source.id
WHEN MATCHED THEN UPDATE SET Target.id = Source.id
WHEN NOT MATCHED THEN INSERT(id) VALUES(Source.id)
WHEN NOT MATCHED BY SOURCE THEN DELETE

 OUTPUT $action
, inserted.id 
, deleted.id
 )
AS Changes (Action, idNew, idOld)  ;

select * from mylog
select * from mytest


 

You can find a good article from Arron Bertrand about some issues he found:
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s