BULK INSERT error handling (?)

Question

Post reply

BULK INSERT error handling (?)

Barkingdog

SSCoach

Points: 18670
More actions
November 16, 2004 at 9:36 am

#59800

I’m writing a routine that BULK INSERT’s records into a “preview table”. Occasionally, we get a record with an invalid date field, such as “00/00/00”. Does BULK INSERT have any error handling capability at all? I’d like to send this erroneous record to an “error” table and import the remaining good records.

TIA,

Bill

Viewing 3 posts - 1 through 2 (of 2 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

See, if this helps:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

If you format the "preview" table (also known as a "staging table") correctly, BCP will catch the kinds of errors you mentioned. Dates should always go into a DateTime data-type column. If an invalid date tries to enter, the table and data-type will reject the entire record. If you setup BCP correctly, you can actually divert these error records to an error file. You need to use some of the parameters available with the BCP command... these are straight out of "Books on Line"...

-e err_file

Specifies the full path of an error file used to store any rows bcp is unable to transfer from the file to the database. Error messages from bcp go to the user's workstation. If this option is not used, an error file is not created.

-m max_errors

Specifies the maximum number of errors that can occur before the bulk copy operation is canceled. Each row that cannot be copied by bcp is ignored and counted as one error. If this option is not included, the default is 10.

--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)