Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sequence of Merge Inserts, Updates and Deletes Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 11:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 29, 2015 1:01 PM
Points: 26, Visits: 208
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.
Post #1382102
Posted Wednesday, November 7, 2012 12:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 15, 2015 6:10 AM
Points: 13,872, Visits: 9,606
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
Post #1382120
Posted Wednesday, November 7, 2012 12:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 29, 2015 1:01 PM
Points: 26, Visits: 208
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.
Post #1382141
Posted Wednesday, November 7, 2012 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 15, 2015 6:10 AM
Points: 13,872, Visits: 9,606
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
Post #1382147
Posted Thursday, November 8, 2012 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 29, 2015 1:01 PM
Points: 26, Visits: 208
Thanks Joe!
Post #1382593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse