SqlBulkCopy vs inserts?

sune42 posted on Monday, March 12, 2007

Have anyone tried to use the SqlBulkCopy operation to do multiple inserts into a database?

For example if I want to insert 100 rows into a database?

Is it faster ? than doing single inserts in a loop? (where single inserts could be call to a StoredProc that does the insert)

Or is it so that what is sent over to the database is still single inserts, but its more a programmatic way to send multiple stuff to the DB?

phowatt replied on Monday, March 12, 2007

I am developing a deployment package in which I have to add small amounts of data to a SQLExpress database that is being created in the deployment process. I am using bulk copy to do the task but in my reasearch I discovered that using Bulk Copy or SqlBulkCopy is faster than single inserts and in my testing I find the Bulk Copy to be very fast.

xal replied on Monday, March 12, 2007

I had to import some HUGE text files in one app as a regulare process in one app. The files weren't CSVs, they had custom formatting rules. So I implemented IDataReader in a class for reading these custom txt files and passed that to the SqlBulkCopy and it was amazingly fast.

I hear there's a way to tell a dataadapter to batch inserts which makes that go faster too, but I don't recall what the property was...

Andrés

DW_CT replied on Monday, March 12, 2007

Be aware that the SQLBulkCopy leverages the old bcp utility. As such, it typically performs "minimally-logged" inserts, and by default does not support full transactional capabilities. There is information regarding this in the help files. If you're using SQLBulkCopy to do something like a large nightly data load, I'd recommend doing a full DB backup, the bulk copy, and another full DB backup if there were no errors (or a restore if there were). If you're using it for small inserts, I'd think hard about what you might be losing in data integrity if it fails mid-way through a batch.