MERGE INTO WITH FILTERED SOURCE does not work properly - by Tom Groszko

Status : 


Sign in
to vote
ID 633132 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 12/23/2010 7:53:42 AM
Access Restriction Public


MERGE INTO when the source is filtered does not work as expected. BOL identifies a caution when filtering on the TARGET but not the SOURCE. BOL should be changed or the problem should be fixed. Example code is attached.
Sign in to post a comment.
Posted by Microsoft on 1/7/2011 at 5:31 PM
Hello Tom,

Thank you for filing the feedback! Indeed the issue you are describing is by design and we should improve the documentation (Books Online). This bug will track that work.

In your example the plan for merge uses left outer join between source and target tables with filter applied as part of the outer join. Because of this, rows that do not qualify get into “when not matched by target” action causing the behavior you have observed.

There are number of ways to work around this: by either pushing the filter (WorkMarker =”X”) on source table using filtered select (as in your second MERGE query) or you can combine filter conditions in the WHEN [NOT] MATCHED clauses themselves.

Thank you and best regards,
Boris Baryshnikov.
SQL Server Engine