Sunday, October 5, 2008

In The Spirit Of Sharing Information: SQLBulkCopy Class in .NET

I had an annoying problem at work the other day; how to copy a subset of data from one SQL Server database to another. This set included data from six tables. I looked at my options:
  • bcp (bulk copy) command-line utility
  • Dynamic SQL (select "insert into items (" + items.* + ")")
  • write a c# app to load the data into a dataset, then insert it into the new DB.
  • Use the Import/Export Wizard.
The first three didn't look particularly appealing, and the wizard only exports queries to data files; I'd have to do a lot of work to get those files to work.

Then I discovered the SqlBulkCopy class. In the .NET Framework 2.o and beyond, the SqlBulkCopy class gives bcp functionality in the managed code world. It made my life much easier.

No comments:

All rights reserved. Take that!