Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?

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.

like image 635
Robson Rocha de Araujo Avatar asked Feb 25 '11 12:02

Robson Rocha de Araujo


2 Answers

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.

like image 74
gbn Avatar answered Sep 27 '22 17:09

gbn


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

like image 35
SQLMenace Avatar answered Sep 27 '22 16:09

SQLMenace