BULK INSERT batches vs. BCP batches v7.0

There are two distinctly different types of behavior between these two data import processes when using the batch options. One will leave your data in a consistent state and the other may not. Batches are normally used to speed the process of the data import. The idea is that SQL Server handles small transactions better than large ones.

BULK INSERT is an "in-process" application meaning that it executes within the scope of the existing SQL Server process/session. Using the BATCHSIZE = option, you can wrap a bulk insert statement in a BEGIN TRAN and COMMIT/ROLLBACK statement and it will behave as any normal insert. If for any reason the BULK INSERT statement fails, it will ROLLBACK and provide you with the "all or nothing" behavior of a regular transaction.

BCP.EXE, on the other hand, is a different process than BULK INSERT. BCP.EXE executes as an "out of process" application. This type of application does not operate within the scope of the existing SQL Server process/session. BCP.EXE actually requires execution from an external mechanism such as a command window or the xp_cmdshell extended stored procedure. It requires the establishment of its own session each time it executes. This means some additional overhead of connection is required for BCP.EXE's execution. Because it is an out-of-process application, it cannot be wrapped within a T-SQL BEGIN TRAN and COMMIT/ROLLBACK transaction.

BCP.EXE will behave similarly to BULK INSERT with the absence of the command line option of -b. By not specifying this option, BCP will behave like a transaction, meaning it will provide you with the "all or nothing" transaction.

Be careful when specifying the -b option value with BCP since you could conceivably import several batches into a table. If for any reason BCP fails, these sent batches will remain in the table, leaving your data in an awkward state.

Home   Previous���Next

Hosted by www.Geocities.ws

1