« Introduction to Change Data Capture | Main | To MERGE or not to MERGE in SQL Server 2008 »
October 29, 2009
To MERGE or not to MERGE in SQL Server 2008
I was reviewing some demo material for the MERGE statement that Jim Pletscher put together. It was a standard scenario where we wanted to UPDATE, DELETE, or INSERT rows from a source table to a target table. A general overview of this is available here.Inserting is harmlessbut what if sometimes we don't want toupdate or deletesomething from our target table?
My first step was to add a new column to the target table. I named this column "DoNotTouch" and made it a bit defaulting to zero (0). I then set this value to one (1) on one of the target table rows.
So now how do we tell the MERGE statement to not touch anything where this bit is flipped on? You cannot use a WHERE clause in the MERGE statement. The answer is within the WHEN clause of the MERGE statement. All you need to do is add an additional condition to each WHEN for the update and delete operations:
(TargetTable is the name of the target table and SourceTable is the name of the source table)
WHEN MATCHED AND TargetTable.DoNotTouchlt;gt; 1 THEN
UPDATE SET TargetTable.FirstName = SourceTable.firstname,
TargetTable.LastName = SourceTable.lastname
WHEN NOT MATCHED BY SOURCE AND TargetTable.DoNotTouchlt;gt; 1 THEN
DELETE
So that worked! It did not touch the row where I set the DoNotTouch flag to one (1).
Posted by Steve Mann at October 29, 2009 11:03 AM
Twitter 
LinkedIn 
YouTube