I am trying to bulk insert data from a file to MS SQL Server using the bcp utility but it is not functioning as I expect it to with regard to errors. I want to understand what I'm doing incorrectly or where my assumptions are incorrect.
With the below command:
bcp mydatabase.dbo.mytable IN "c:\infile.txt" -f "c:\infile.fmt" -T -m0 -S myserver -e "c:\errors.txt"
I expect bcp to include all records in the file in a single batch and to fail after encountering the first error and to report the errors in the error file. It however completes by importing as many records as possible through the whole file. It doesn't stop or roll-back any transaction.
If I change the -m option to -m 1, then it does fail after the first record, but does not roll back the batch at all (e.g. the records in the batch imported prior to the error are persisted in the import table after the bcp finishes).
The documentation of bcp appears to indicate that the batch would be rolled back on encountering the first error, and that the batch will include all records in the file if no batch size is specified.
The bcp utility version is 13.0.1601.5.
from:Microsoft BCP Utility Documentation
-b batch_size
Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that
imports the whole batch before being committed. By default, all the
rows in the data file are imported as one batch. To distribute the
rows among multiple batches, specify a batch_size that is smaller than
the number of rows in the data file. If the transaction for any batch
fails, only insertions from the current batch are rolled back. Batches
already imported by committed transactions are unaffected by a later
failure.
and
-m max_errors
Specifies the maximum number of syntax errors that can occur before the bcp operation is canceled. A syntax error implies a
data conversion error to the target data type. The max_errors total
excludes any errors that can be detected only at the server, such as
constraint violations.A row that cannot be copied by the bcp utility is ignored and is
counted as one error. If this option is not included, the default is
10.
Best Answer
A rollback of a BCP batch transaction occurs when SQL Server errors occur during the bulk insert (e.g. constraint violations) but not due to client side errors (e.g. data type conversion during BCP parsing). Erred records are not sent to the server and are not part of a transaction.
SQL Server errors will not only rollback the current batch but abort the entire BCP operation.