Error handling

Question

Post reply

Error handling

Khades

SSCrazy

Points: 2067
More actions
February 25, 2011 at 11:44 pm

#232663

I need to dump some rows into a staging table.
However I want sql server to continue with the rest of the rows when 1 or more rows return an error.
Is there any way I can do this without using any outside tools?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Yes, it's possible:

Option 1:

Find the logic that will cause an error and exclude those rows before you perform the insert.

Option 2:

Since it's a staging table, you could remove the reasons for getting the error (e.g. change suspicious columns to varchar/nvarchar) even if those columns should only hold numerc data.

As a side note: I recommend to change the way you think about dealing with this insert process. You shouldn't think about what can go wrong with a single row. Think about it what columns can cause an error and react accordingly. Thinking about a row at a time is considered RBAR (Row-By-Agonizing-Row) and will usually lead to poor performance due to inefficient code.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150457 More actions · Answer 2

Khades (2/25/2011)
I need to dump some rows into a staging table. However I want sql server to continue with the rest of the rows when 1 or more rows return an error. Is there any way I can do this without using any outside tools?

Depends what you mean by 'outside tools'. SSIS is part of SQL Server and has the feature you need built-in.

One other pure TSQL method not mentioned would be to bulk copy the rows out to a file and then bulk copy them back in, specifying the maximum number of errors you can tolerate, and where to route the error rows. I would use SSIS.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Khades SSCrazy Points: 2067 More actions · Answer 3

SQLkiwi (2/26/2011)
Khades (2/25/2011)
I need to dump some rows into a staging table. However I want sql server to continue with the rest of the rows when 1 or more rows return an error. Is there any way I can do this without using any outside tools?
Depends what you mean by 'outside tools'. SSIS is part of SQL Server and has the feature you need built-in.
One other pure TSQL method not mentioned would be to bulk copy the rows out to a file and then bulk copy them back in, specifying the maximum number of errors you can tolerate, and where to route the error rows. I would use SSIS.

I would use SSIS too. But it was not the option for this project (I don't know why). By outside tools I meant SSIS. But I didn't want to say SSIS strictly and then have someone suggest Informatica or another ETL tool. 😀

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

As of 2005 (IIRC), Bulk Insert (and BCP since way back when) will let you identify an errata file to save errored rows in. You simply tell Bulk Insert to allow an impossibly large number of error rows and the Bulk Insert will load the "good" rows in staging and send the bad rows (and some collateral damaged) rows to a bad file for "rework".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions