Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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.

0 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 5

<=-=Jan 7 2011 5:31PM=-=>

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

0 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base