BULK INSERT Vs BCP.exe

We have the following code written in the TSQL using the Bulk Insert command which works fine in SQL Server 2000 and 2005

Code Snippet

BULK INSERT MYTABLE FROM 'C:\TextDataFiles\MYTABLE.txt' WITH ( FORMATFILE = 'C:\MYTABLE.fmt' , MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\MYTABLE.bad')

Please note the MAXERRORS is set to 0. So, if a error occurs nothing is written to the table.

Now we are trying to convert this functionality using the bcp.exe (actually using the API like bcp_control in C++) with the following code

Code Snippet

bcp "MYTABLE" in "'C:\TextDataFiles\MYTABLE.txt' -m0 -f"'C:\MYTABLE.fmt' -e 'C:\MYTABLE.bad' -b500000 -SMYSERVER -Uuid -Ppwd -h"CHECK_CONSTRAINTS"

This code does not work the same way as the BULK INSERT code above. It default the max errors to 10 (as documented) and inserts row(s) before the first error. How can I have the same functionality, fail on first error, with the bcp?

Your help is appreciated.

Since it will still insert the rows that are processed before the first error occurs (then failing and halting the insertions) I would probably push the data to a temporary table, do a check to make sure all data was inserted properly, then move the data from the temp table to the production table. This way you won't get half of the data if the bcp fails.

|||That is the way it works because of the batch=500000. You will always get the "batch" inserted before the error occurs.

|||

Why is the behaviour different in the TSQL for BULK INSERT, we have the same value for the BATCH. Is it possible BATCH INSERT is started in a commitment boundary automatically?. I do not understand why there is a different in the behaviour between the 2 commands.I would like to keep the existing functionality, as it, if possible, with out making significant changes like inserting to temp table and then moving to the perminant tables.

|||The Bulk Insert command creates an "implicit" transaction around the command. So if there is an error, everything gets rolled back. bcp.exe does not.

If you remove the "-b500000" from the bcp.exe command, you will get all or nothing.
sql