September 1, 2010 at 2:05 pm
When you do a
MERGE ...
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...
is the match based on the data present in the target table before the MERGE starts, or does it take into account data inserted during the merge?
The reason I'm asking is that I'm merging data from two sources, and it has some duplicates. I'd assumed one of a pair of duplicate values would INSERT, then the other - because the data is now in the target table - would UPDATE. But it seems both INSERT, and I'm getting duplicate values in my key field.
I suppose that as a set operation, it would be logical for the MATCH to be based on the state of the target table prior to the MERGE starting, but I can't find anything to confirm that's how it is supposed to work.
September 1, 2010 at 3:38 pm
It is a set-operation and it looks like MERGE internally divides the records into three streams based on current data.
One stream for DELETE, one stream for UPDATE and one stream for INSERT.
See more here http://weblogs.sqlteam.com/peterl/archive/2008/11/24/SQL-Server-2008-with-MERGE-and-triggers.aspx
N 56°04'39.16"
E 12°55'05.25"
September 1, 2010 at 3:44 pm
So basically it divides the data based on the MATCH condition before it does any INSERT/UPDATE/DELETE. Hence the match taking no notice of the changed data values.
Thanks; I think that answers my question.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply