Multiple inserts in a single transaction

Hello All,
I have an application where i am doing multiple inserts inside a single transaction. Now to do this I have typically two options. One of them is to start a transaction from my application, use a loop and do inserts one by one and commit it once the last insert is done. This seems good but involves constant round trip from my application server to my database server. Other drawback is that if my applications fails (for instance there is a power failure at client) that there will be waiting involved for other queries as inserts hold exclusive locks.
The other approach is to write a stored procedure which takes xml as input. Now my application will send an xml file this SP and inside the SP using a cursor i will loop through and do the insert inside a single transaction.
Currently following the first strategy, would like to know whether the second strategy make sense and is better than the first one.
Thanks in advance :)

The second strategy is much improved.

The total time that the data will be locked in the transaction will be shortened by all of the network roundtrips currently experienced. If the process fails, it fails on the server and the TRANSACTION can be immediately rolled back, releasing the locked resources much sooner.

|||Hey Arnie,
Thanks a lot. Had though on similar lines and now it just adds to my confidence on using the second approach. was just concerned with the way SQL server will handle the xml file|||

In addition, you might see further performance improvements if (inside the stored procedure) you perform the inserts in a set-based manner rather than by using cursors.

Chris

|||Hy Chris Thanks for the reply.
By the way i am totally unaware of the set-based inserts from xml. If u can plz elaborate|||

Are you using SQL Server 2000 or SQL Server 2005?

Also, could you post a sample of your XML and describe how the data contained within the XML maps to your table structure?

Chris

|||Hey Chris thanks for the pointer, I directly inserted the values into the table instead of looping through the cursors. and it worked just as well.
FYI i am using SQL Server 2005|||Hi, I'm doing something similar to this, only I'm currently using the first option Metesh mentioned. I'd like to use the second, but am not sure how to handle xml in my stored procedures, so that I can "directly insert" the values into the tables. Can anyone point me to some good resources? Thanks!|||

A good starting point would be the OPENXML topic in SQL Server 2005 BOL:

http://msdn2.microsoft.com/en-us/library/ms186918.aspx

Chris

sql