I'm unable to test it and get the solution by myself right now, and i haven't found the information on MSDN nor at google.
I was questioned if a merge statement that inserts, deletes and updates records in a given table need to be encapsulated in a transaction (if a failure occurs after the inserts were done, during the updates for example), or if any of the operations fails the whole merge fails as well.
Maybe it would not harm if we included an transaction, but for the sake of curiosity, we wish to know more about the merge internals.
Any statement in SQL Server is a transaction in it's own right.
That is, it is atomic: everything succeeds or everything fails
An explicit transaction would be used to group multiple single atomic statements into one big atomic transaction.
This is beauty of MERGE: no need for an explicit transaction and 3 separate statements.
All DML statements in SQL Server are ran in an implicit transaction if an explicit is not started. Of course you can still wrap it in your own explicit transaction but shouldn't be needed
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With