sqlbulkcopy batch sizing in relation to number of rows in datatable

c# c#-4.0 sqlbulkcopy

Question

Do i need to ensure that each batch is completely full? ie if i had a datatable with 12028 rows, could i just use a batch size of 10k and it would automatically create 2 batches, 1 with 10k in and 1 with 2028 in?

Thanks.

Accepted Answer

According to MSDN: SqlBulkCopy.BatchSize --

If the SqlBulkCopy instance has been declared without the UseInternalTransaction option in effect, rows are sent to the server BatchSize rows at a time, but no transaction-related action is taken. If UseInternalTransaction is in effect, each batch of rows is inserted as a separate transaction.

The BatchSize property can be set at any time. If a bulk copy is already in progress, the current batch is sized according to the previous batch size. Subsequent batches use the new size. If the BatchSize is initially zero and changed while a WriteToServer operation is already in progress, that operation loads the data as a single batch. Any subsequent WriteToServer operations on the same SqlBulkCopy instance use the new BatchSize.

Based on this information, I am inclined to believe that setting batch size to 10k rows and you provide 12k+, multiple batches will be created.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why