MERGE a subset of the target table — Pitfall and Options

I have used MERGE for sometime now. It is a very useful feature added to SQL Server 2008.  I have used MERGE to come up with some solutions to my real production code.

When it is time to use a subset of the target table to manipulate, it is attempting to add the condition to the ON clause like to filter our some records for the target table. But this is not the correct way to do it.

I have run into this issue for a question posted at MSDN:

Tom Cooper pointed out the issue in another related thread:


To better understand this issue, I serached to find a detailed example from Paul White to discussing about this issue:

Here is a fully documented link from MSDN:

and the definition of MERGE documentation:


To solve the problem, we have three options: Use the filter at the WHEN clause; use an UPDATABLE View; use CTE.

I would like to use WHEN if I want a quick fix. To make it more clear, I will write the code starting with a CTE block before the MERGE keyword and use the CTE  as target table.





Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s