November 7, 2012 at 11:29 am
Does anyone know if there is any predictable order that the Merge statement will apply inserts and updates? I assumed that it would apply them in the order listed in the WHEN clauses, e.g. WHEN NOT MATCHED THEN INSERT followed by WHEN MATCHED THEN UPDATE would result in inserts first, then updates.
However, I think I'm seeing behavior that indicates it could be a mix, e.g. some rows are inserted, some are updated, then some more inserted, etc. MERGE documentation seems to suggest this but it's in the context of using TOP so it's not clear to me. http://technet.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
The TOP clause is applied after the entire source table and the entire target table are joined and the joined rows that do not qualify for an insert, update, or delete action are removed. The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions are applied to the remaining joined rows in an unordered fashion. That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted and so on.
Any insight appreciated.
November 7, 2012 at 12:10 pm
There is no guaranteed sequence to the actions. The whole idea of using Merge is that it makes it into a single, atomic action as far as your code is concerned. There may be sequences behind the scenes in the depths of the engine (after all, CPUs process instruction sets in a sequential fashion), but there's no way for you to use Merge to enforce "add these, then update these, then delete these" or anything like that.
If you need that kind of sequentiality, split the commands up into separate Insert/Update/Delete statements.
Any particular reason you need a specific sequence? Or is this just curiosity asking?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 7, 2012 at 12:54 pm
Thanks GSquared, appreciate the confirmation; it explains the behavior I experienced.
The reason...a particular target table has a unique constraint on three columns (not the surrogate primary key), say SomeNumber, SomeName, SomeState. The source was updated as follows: User updated a record (AA) SomeName column to a new value. User added a new record (BB) using the same original SomeNumber, SomeName, SomeState values as record AA.
When the MERGE runs (joined on the surrogate primary key), the desired effect on the target is for it to update the original AA record with the new SomeName value, and insert the new BB record. However, if it tries to insert BB before it updates AA the unique constraint will reject it. If it updates then inserts, all is well.
It looks like my work around will be to break the merge apart, as you mentioned. Thanks again.
November 7, 2012 at 1:10 pm
Yeah, that's a good case for breaking it up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2012 at 9:47 am
Thanks Joe!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply