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