Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequence of Merge Inserts, Updates and Deletes


Sequence of Merge Inserts, Updates and Deletes

Author
Message
Bruce Hendry
Bruce Hendry
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 247
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14363 Visits: 9729
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
Bruce Hendry
Bruce Hendry
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 247
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14363 Visits: 9729
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
Bruce Hendry
Bruce Hendry
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 247
Thanks Joe!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search