How does Sqlbulkcopy work?

Question

I’m using in my class project SqlBulkCopy to mass input data. Everything has worked perfectly, but in recommending this to my co-workers, despite proving the efficiency, questioned about the functioning.

In my research, I was able to verify that the SqlBulkCopy works similar to the bcp utility. So I went for information, but I couldn’t find anything very detailed.

To better understand, I started the SQL Server Profiler to try to capture the generated querys, but to my surprise, apparently the data that was entered were not sent via SQL command.

Now I’m even more confused. The SqlBulkCopy works with some kind of service provided by bcp utility, sending it the data to be entered? How is this mass insertion work done? How can this be so fast?

While entering the data, is there any risk of the tables getting stuck? And if there is an error during the process, how does it behave?

Answer 1

The SqlBulkCopy works with some kind of service provided by bcp utility, sending it the data to be entered?

The source of the class SqlBulkCopy is here. Search for INSERT BULK within this source. It is the form it uses to perform the insertion.

How is this mass insertion work done?

Through a command similar to this one.

How can this be so fast?

SQL Server skips a series of checks and simply accepts the data as correct, which makes it a simple organized write operation, much faster than a traditional insert.

While entering the data, there is some risk of the tables getting stuck?

They get partially locked. Bulk Insert asks for lock unique in a partition in the table, but there is still the option of lock exclusive (see here about TABLOCK).

And if any error occurs during the process, how it behaves?

It depends on how you perform this. If it is within a transaction you set, an error rolls back all modifications. If it is without a defined transaction, each batch successfully executed receives a commit transactional, and a rollback if there are errors. That is, in the case of batches successfully and batches failed, the insertion will be partial.