Identify failure record in a XML bulk insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Identify failure record in a XML bulk insert

Author

Topic

rajselvaraj7
Starting Member

5 Posts

Posted - 2010-05-13 : 05:49:14

Hi,

I have a stored procedure which reads from a XML and inserts multiple records into a table, I need a help on this. In case of a failure during insert is there a simple way to identify the record that failed.
My simplified version of the stored procedure looks like this,

******************************
EXEC sp_xml_preparedocument @XMLHandle OUTPUT,@XMLInput

INSERT INTO dbo.EmpName
(Sno,
Empname)
SELECT
Sno,
Empname
FROM
OPENXML(@XMLHandle,'/EmpData/EmpDataRow',3)
WITH
(Sno int,
Empname varchar(25))

EXEC sp_xml_removedocument @XMLHandle
******************************

Two things I need here,
1. In case of a failure I need to identify which record falied and
2. In case of a faliure I need to rollback the transaction.

Any help would be of much use to me.

Thank You.

-Rajesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:33:01

what type of failures are you expecting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajselvaraj7
Starting Member

5 Posts

Posted - 2010-05-17 : 04:05:20

Thanks for the response, it could be any failure like an insert failed due to primary key violation or bad data or such things. If you have any idea on this please provide your thoughts.
Thanks,
Rajesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:03:33

nope you cant catch pk violations as it will cause termination of program execution. but instead what you could do is do check before actual insertion to look for already existing values and flag them as potential pk violation error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajselvaraj7
Starting Member

5 Posts

Posted - 2010-05-18 : 00:28:58

Thanks again Visakh, even if the program execution terminates is there a way by which I can identify the failed record by way of @@trancount or someother way.
I just need to identify which record caused the failure (or for ex. something like it failed to insert on the 8th record) will it be possible to identify this even if the program terminates.
Regards,
Rajesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 01:03:47

nope it will cause entire batch to be rolledback so if you need to identify records which causes PK violation then only thing you can do is like what i suggested before.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajselvaraj7
Starting Member

5 Posts

Posted - 2010-05-18 : 08:16:54

ok,thanks Visakh

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-18 : 09:35:59

Perhaps ram them all into a generic staging table that has no constraints, then attempt your insert from the staging table. You should be able to see which record is bad from there. There is probably a better way, but that is the first thing i can think of on short notice.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rajselvaraj7
Starting Member

5 Posts

Posted - 2010-06-01 : 07:18:05

Thank you DonAtWork for your response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources