How does SqlBulkCopy work?

Question

How does SqlBulkCopy work?

Navigation

#1 by (5 votes)

5

I am using the SqlBulkCopy class for do the bulk data insertion. Everything has worked perfectly, but in recommending this to my co-workers, while proving efficiency, they questioned how it works.

In my searches, I could see that SqlBulkCopy works similar to bcp utility . So I went after information, but I did not find anything very detailed.

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

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

During data insertion, is there any risk that the tables will be locked? And if an error occurs during the process, how does it behave?

sql-server c# sqlbulkcopy

asked by anonymous 06.06.2016 / 19:06

1 answer

The code generated by the GUI builder of netbeans affects application performance? How does auto increment work with composite keys in the MyISAM engine?

score 5 · Answer 1

Does SqlBulkCopy work with some kind of service provided by the bcp utility , sending it the data to be inserted?

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

How is this bulk insertion work done?

Using 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, making it a simple, organized, writing operation, much faster than a traditional insert.

When inserting the data, is there any risk of the tables being locked?

They get partially blocked. Bulk Insert requests exclusive on a partition in the table, but there is still the lock option (see here about TABLOCK ) .

And if an error occurs during the process, how does it behave?

It depends on how you do this. If it is within a transaction defined by you, an error rolls back all modifications. If it is without a defined transaction, each successful batch receives a commit , and a rollback error. That is, in the case of batches successfully and batches failing, the insertion will be partial.