Quickest Way to Copy SQL Data

Question

I could use some suggestions / ideas. I wrote a console application that queries all data from a table in MS Access (I know, but I inherited it) to a SQL table. It runs every morning as a scheduled task. The fields between the two tables are not identical. Currently I select all data from the MS Access table, loop through the dataset and insert each row into the SQL table. I also write a quick log file in the process. It works, but it's not fast. I would appreciate any ideas that you may have to improve the process. Thanks!

Answer 1

SqlBulkCopy Class

It's way faster than individual insert statements.

You have to increment your own identity field value for the primary key. To do that, first grab the last identity field value where you left off:

select top 1 id_customer from customers order by id_customer desc

Then increment an int variable as you loop through your DataSet.

Or you can use GUID for primary key column instead.

Example code for using SqlBulkCopy:

public static void BulkLoadDataTable(DataTable table, string destinationTable)
{
  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
  {
    bulkCopy.BulkCopyTimeout = 1200;
    bulkCopy.DestinationTableName = destinationTable;
    bulkCopy.WriteToServer(table);
  }
}

Strongly Typed DataTable:

using System;
using System.Data;

public class CustomersDataTable : DataTable
{
  public CustomersDataTable() : base()
  {
    base.TableName = "Customers";
    Columns.Add(new DataColumn("id_customer", typeof(int)));
    Columns.Add(new DataColumn("first_name", typeof(string)));
    Columns.Add(new DataColumn("last_name", typeof(string)));
    Columns.Add(new DataColumn("active", typeof(bool)));
  }
}

Answer 2

Have you looked at SSIS packages? I would look into that first. If you don't have access to that, then look into the SqlBulkCopy class mentioned here already.

You could also look into using SELECT statement with an INSERT.