MERGE INTO WITH FILTERED SOURCE does not work properly
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.
Upvotes: 5<=-=Jan 7 2011 5:31PM=-=>
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,
SQL Server Engine