how to insert 61000 data objects into sql server 2005?

Question

I am trying to insert 61,000+ objects, obtained via a remote call, into a SQL Server 2005 database in the fastest method possible. Any suggestions?

I have looked at using SQLBulkCopy, but am having a few problems working out how to get the data into the right format since I am not starting with a DataTable, but instead have a list of objects. So if answers could contain code samples that would be appreciated.

I am trying to insert the data into a temp table before processing it to keep memory usage down.

Edit...

@JP - this is something that will run every night as a scheduled batch job with an IIS ASP.NET application.

Thanks.

Answer 1

If this is something you are doing one time or only periodically, you should look at using SSIS (it's basically DTS on steroids). You could build a package that gets the data from one datasource and inserts it into another. There are also features for stop/start and migration tracking. Without more details on your situation, I can't really provide code, but there are a lot of code samples out there on SSIS. You can learn more and play around with SSIS in Virtual Labs.

Answer 2

If you intend on using the SQLBulkCopy class I would suggest that you create a custom class that implements IDataReader that will be responsible for mapping the 61000 source data objects to the appropriate columns in the destination table and then using this custom class as a parameter to the SQLBulkCopy WriteToServer method.

The only tricky part will be implementing the IDataReader interface in your class. But even that shouldn't be too complicated. Just remember that your goal is to have this class map your 610000 data objects to column names. And that your class will be called by the SQLBulkCopy class to provide the data. The rest should come together pretty easily.

 class CustomReaderClass : IDataReader
 {
    // make sure to implement the IDataReader inferface in this class

    // and a method to load the 61 000 data objects
    void Load()
    { 
       // do whatever you have to do here to load the data..
       //  with the remote call..?!
    }
 }

 //.. later you use it like so

 SQLBulkCopy  bulkCopyInstance;
 CustomReaderClass aCustomReaderClass = new aCustomReaderClass();
 aCustomReaderClass.Load();

 // open destination connection
 //  .. and create a new instance of SQLBulkCopy with the dest connection
 bulkCopyInstance.WriteToServer(aCustomReaderClass);

 // close connection and you're done!

I hope the above "pseudo-code" makes some sense..

Answer 3

@Miky D had the right approach, but I would like to expand the details. Implementing IDataReader is not really that hard.

To get IDataReader working with a bulk inserter you should look at implementing:

• Dispose();
• FieldCount {
• object GetValue(int i);
• GetSchemaTable();
• Read();

The rest can be stubs that throw NotImplementedExceptions, see this sample

Getting the schema table is also pretty easy. Just select one row from the target table and call GetSchemaTable().

To keep stuff clearer I like to have an abstract class that throws NotImplementedException on the non essential methods, perhaps down the line that abstract class can implement the missing bits for added robustness.

A couple of BIG caveats with this approach:

1. Which methods to implement is not documented in SQLBulkCopy
2. With the follow on that, in later versions of the framework/hotfixes or service pack may break you. So if I had mission critical code I would take the bite and implement the whole interface.

I think, that its pretty poor that SQLBulkCopy does not have an additional minimal interface for bulk inserting data, IDataReader is way to fat.