Originally, the bcp parameter maxerrors had to be set to a high value and the parameter batchsize set to 1, to identify the rows that were rejected due to exceptions such as duplicate rows or errors in the batch file. This method was not efficient and made it hard to identify, debug, and reload the rows that were rejected.
With the introduction of the new bcp option -d discardfileprefix you can now log the rejected rows into a dedicated discard file that has the same format as the host file. The discard file is created by appending the input file name to the discard file prefix supplied. You can correct the rows in this file and use it to reload the corrected rows.
bcp -d discardfileprefix
This example creates the discard file reject_titlesfile.txt:
bcp pubs2..titles in titlesfile.txt -d reject_
Specifying the -d option applies only when bulk copying in; it is silently ignored when used in bulk copying out.
If you use multiple input files, one discard file is created for every input file that has an erroneous row. If there are no rejected rows, no discard file is created.
If bcp reaches the maximum errors allowed and stops the operation, the bcp logs all the rows from the beginning of the batch until the failed row.
If the discard file option is specified, the batch size
is automatically adjusted and the message “Warning!!! Batch
size adjusted to the value newbatchsize, for
the optimization of the discard file feature.” is displayed, when:
-b batchsize is specified but the batch or row size is too big to hold all the rows of the batch in memory.
The -b batchsize option is not specified.
"Unable to open the discard-file discardfilename."
"I/O error while writing the bcp discardfilename."
"Unable to close the file discardfilename. Data may not have been copied."
