When you specify the -e error_file option with copy in, bcp stores the rows that it cannot copy in to Adaptive Server in the specified error file.
The error file stores:
A line that indicates which row failed and the error that occurred, and
A line that is an exact copy of the row in the host file.
If the file name specified after -e already exists, bcp overwrites the existing file.
If bcp does not encounter any errors, it does not create the file.
bcp in detects two types of errors:
Data conversion errors
Errors in building the row; for example, attempts to insert a NULL into columns that do not accept null values or to use invalid data formats, such as a 3-byte integer
The copy in process displays error messages on your monitor.
The following example loads the newpubs file into the publishers database, storing any error rows in the pub_err file:
bcp pubs2..publishers in newpubs -epub_err
Keep the following in mind when working with error files generated by copy in:
bcp stores rows in an error file only when the bcp program itself detects the error.
bcp continues to copy rows until bcp encounters the maximum number of error rows, at which point bcp stops the copy.
bcp sends rows to Adaptive Server in batches, so bcp cannot save copies of rows that are rejected by Adaptive Server, for example, a duplicate row for a table that has a unique index.
Adaptive Server generates error messages on a batch-by-batch basis, instead of row-by-row, and rejects the entire batch if it finds an error.
It is not considered an error for Adaptive Server to reject duplicate rows if either allow_dup_row or ignore_dup_key was set when a table’s index was created. The copy proceeds normally, but the duplicate rows are neither stored in the table nor in the bcp error file.
