2

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.

2

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.

  • thanks for your answer. Thanks makes sense to me now you explain it that way. – Jayden Apr 3 '18 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.