Would this rollback/stop all records from inserting?

Question

Hi

I been going through this tutorial

http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx

and them make a SP like this

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText)
AS 
 DECLARE @hDoc int   

 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

 INSERT INTO TBL_TEST_TEST(NAME)
 SELECT XMLProdTable.NAME
    FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)   
       WITH (
                ID Int,                 
                NAME varchar(100)
            ) XMLProdTable

EXEC sp_xml_removedocument @hDoc

Now my requirements require me to mass insert and mass update one after another. So first I am wondering can I merge those into one SP? I am not sure how it works with this OPENXML but I would think it would just be making sure that the XPath is right.

Next what happens while it would be running this combined SP and something goes wrong. Would it roll back all the records or just stop and the records that happened before this event that crashed it would be inserted?

Answer 1

A transaction is atomic. Either all inserted records are commited, either all are rolled back. A statement will always do the updates as part of a transaction. So this INSERT is either all going to commit, or is going to rollback and no row att all is going to be inserted.

In SQL 2005 you should avoid using NTEXT types and OPENXML. They inneficient and NTEXT is actually deprecated, and there are much better alternatives:

• use XML datatype instead of NTEXT
• use the XML data type methods instead of OPENXML:

.

create procedure usp_insertxml (@data xml)
as
begin
  insert into table (id, name)
  select x.value('ID', 'INT'),
    x.value('NAME', 'varchar(100)')
  from @data.nodes('ArrayOfTBL_TEST_TEST/TBL_TEST_TEST') t(x);
end