Loading multiple rows of data into a SQL server table can be fairly timeconsuming, if for instance you have a data warehouse where all records for a customer have to be reloaded and recalculated at intervals.
At Egghead Café I stumbled upon this article about the SQLBulkCopy object, but unfortunately the code only tests the “after” situation, not how it compares with traditional multiple INSERT statements. Also the timers in the code measure somewhat more than the basic upload, which in my opinion should be changed.
Well, I decided to test it out and the results were striking: trying to execute one big batch of INSERT’s simply made the connection time out, and executing the INSERT statements one at a time in my setup with a non-local SQL server 2005 took about 1½ minutes. Compare that to the SQLBulkCopy which took about 2 seconds!
Conclusion: Look for the SQLBulkCopy object whenever this scenario occurs 🙂