SQLBulkCopy - it's really good!

So here I am - millions of rows of mission-critical data (nearly all text values, yes even the dates, integers and booleans) in about 30 tables: all stored in an antique database, literally from the 1970s, which we can only programatically query through ODBC.

The mission - to get the data into SQL Server 2005 through a combination of the usual tools (SQL and vb.net, .Net 3.5).

Which, after a fair bit of head-scratching and experimenting later, led me to the glorious sunlit uplands of ...

SqlBulkCopy.

System.Data.SqlClient.SqlBulkCopy to give its full name, and it's one of those .Net classes that does exactly what it says on the tin.

Get data from the source (I'm using a sqlDataReader but flat files, datatables, and various other options are also supported); set various parameters to define the batch size, the level of locking, any column mapping, the notification period, the timeout (very important); then call the WriteToServer property, and watch your data fly!

This article on sqlteam.com was my initial route in, but as usual it's well worth reading the relevant bits of MSDN and Technet, and doing some Googling on particular aspects and parameters.

SqlBulkCopy - the LeSession organisation salutes you!