SQL Server Merge statement issue

views:

answers:

SQL Server Merge statement issue

Hello everyone,

I am learning and using SQL Server 2008 new Merge statement, merge statement will compare/operate source table and destination table row by row ("operate" I mean operations performed for when matched or not-matched conditions). My question is whether the whole merge process will be one transaction or each row comparison/operation will be one transaction?

Appreciate if any document to prove it.

thanks in advance, George

+1 A:

The merge statement is a set based operation and as such will operate on the entire set of matching rows.

It is very much like an UPDATE or DELETE. If you want to (and sounds like you do), you can wrap it with a BEGIN TRAN and COMMIT for data integrity purposes.

Raj More 2010-04-28 14:19:04

So, you mean the whole merge will be in one transaction? If yes, any document (e.g. MSDN) to prove? I did not find such document so far.

George2 2010-04-28 14:37:04

@George2 - answer edited

Raj More 2010-04-28 15:05:15

Thanks! After reading your reply, I am still confused. Could you clarify whether the whole merge statement acts as a transaction or each row operation within merge statement acts as a transaction?

George2 2010-04-28 15:37:06

The MERGE statement is atomic, it will either all fail or all succeed, there's no scenario where you would have half of it processed.

SqlACID 2010-04-28 16:26:15

Hello SqlACID, do you have any document to prove?

George2 2010-04-29 06:08:15

@George2, I could not find the documentation. Perhaps you can post at the MSDN forums http://goo.gl/hgl3

Raj More 2010-04-29 13:39:22

Thanks, man! :-)

George2 2010-04-30 07:16:34

ansaurus

tags:

views:

answers:

SQL Server Merge statement issue

related questions