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 07, 2012 11:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 03, 2014 11:55 AM
Points: 24, Visits: 194
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 07, 2012 12:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 07, 2012 12:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 03, 2014 11:55 AM
Points: 24, Visits: 194
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 07, 2012 1:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 Wednesday, November 07, 2012 8:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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.


Nope. SQL is free to do things in any order or in parallel as long as the results are unchanged. This lets it optimize based on the current stats. The theory in the ANSI model is that the original target table is split into MATCHED and NOT MATCHED, which are handled in parallel because they are disjoint sets.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1382239
Posted Thursday, November 08, 2012 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 03, 2014 11:55 AM
Points: 24, Visits: 194
Thanks Joe!
Post #1382593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse