Batch Switches
The bcp utility and BULK INSERT statement accept two switches that allow the user to specify the number of rows per batch sent to Microsoft® SQL Server™ for the bulk copy operation.
| Bcp utility switch | BULK INSERT clause |
|---|---|
| -b batch_size | BATCHSIZE = batch_size |
| -h "ROWS_PER_BATCH = bb" | ROWS_PER_BATCH = rows_per_batch |
The use of these switches has a large effect on how data insertions are logged.
Using the -b Switch or BATCHSIZE Clause
Each batch of rows is inserted as a separate transaction. If, for any reason, the bulk copy operation terminates before completion, only the current transaction is rolled back. For example, if a data file has 1,000 rows, and a batch size of 100 is used, SQL Server logs the operation as 10 separate transactions; each transaction inserts 100 rows into the destination table. If the bulk copy operation terminates while copying row 750, only the previous 49 rows are removed as SQL Server rolls back the current transaction. The destination table still contains the first 700 rows.
Using ROWS_PER_BATCH
If the -b switch or BATCHSIZE clause is not used, the entire file is sent to SQL Server and the bulk copy operation is treated as a single transaction. In this case, the ROWS_PER_BATCH hint or ROWS_PER_BATCH clause can be used to give an estimate of the number of rows. SQL Server optimizes the load automatically, according to the batch size value, which may result in better performance.
Note Generally, the larger the batch size is, the better the performance of the bulk copy operation will be. Make the batch size as large as is practical, although accuracy in the hint is not critical.
If, for any reason, the operation terminates before completion, the entire transaction is rolled back, and no new rows are added to the destination table.
Although all rows from the data file are copied into an instance of SQL Server in one batch, bcp displays the message "1000 rows sent to SQL Server" after every 1000 rows. This message is for information only and occurs regardless of the batch size.
Note Supplying both switches with different batch sizes will generate an error message.
When bulk copying large data files into an instance of SQL Server, it is possible for the transaction log to fill before the bulk copy is complete, even if the row inserts are not logged, from the extent allocation logging. In this situation, enlarge the transaction log, allow it to grow automatically or perform the bulk copy using the -b or BATCHSIZE switch, and set the recovery model to simple. Because only committed transactions can be truncated, this option does not free up space during the bulk copy operation if the -b switch is not used; the entire operation is logged as a single transaction.
The bcp utility and BULK INSERT statement also accept the KILOBYTES_PER_BATCH hint or KILOBYTES_PER_BATCH clause, respectively, which can be used to specify the approximate amount of data (in kilobytes) contained in a batch. SQL Server optimizes the bulk load according to the value set.
Batch sizes are not applicable when bulk copying data from an instance of SQL Server to a data file.