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 harmless but what if sometimes we don't want to update or delete something 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.DoNotTouch<> 1 THEN


               UPDATE SET TargetTable.FirstName = SourceTable.firstname,

                                        TargetTable.LastName = SourceTable.lastname

WHEN NOT MATCHED BY SOURCE AND TargetTable.DoNotTouch<> 1 THEN
DELETE


So that worked! It did not touch the row where I set the DoNotTouch flag to one (1).