The bcp utility and BULK INSERT statement accept two parameters that allow the user to specify the number of rows per batch sent to Microsoft® SQL Server™ for the bulk copy operation.
| bcp utility parameter | BULK INSERT clause |
|---|---|
| -b batch_size | BATCHSIZE = batch_size |
| -h "ROWS_PER_BATCH = bb" | ROWS_PER_BATCH = rows_per_batch |
The use of these parameters has a large effect on how data insertions are logged.
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. If -b or BATCHSIZE are not specified, the entire file is sent to SQL Server and the bulk copy operation is treated as a single transaction.
If the -b parameter 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 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 used.
Note Supplying both parameters with different batch sizes will generate an error message.
When bulk copying large data files into 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 parameter, and set the database option trunc. log on chkpt. to true using sp_dboption or SQL Server Enterprise Manager. Setting this option instructs SQL Server to truncate the transaction log each time it performs a CHECKPOINT, removing the log records for committed batches from the transaction log. Because only committed transactions can be truncated, this option does not free up space during the bulk copy operation if the -b parameter is not used because 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 that 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 SQL Server to a data file.
| bcp Utility | BACKUP |
| sp_dboption | BULK INSERT |
| ImportRowsPerBatch Property | Optimizing Bulk Copy Performance |