The fastest way to copy SQL data

Question

I could use some suggestions / ideas. I wrote a console application that queries all the data from a table in MS Access (I know, but inherited it) into an SQL table. He works every morning as a scheduled task. The fields between the two tables are not identical. Currently, I select all the data from the MS Access table, iterate through the data set and insert each row into the SQL table. I also write a quick log file in this process. It works, but it is not fast. I would appreciate any ideas you might need to improve. Thank!

Answer 1

Class SqlBulkCopy

This is faster than individual instructions insert.

You need to increase the eigenvalue of the identification field for the primary key. To do this, first take the last value of the identifier field where you left off:

select top 1 id_customer from customers order by id_customer desc

Then increase the variable intwhile passing through DataSet.

Or you can instead use the GUID for the primary key column .

Sample 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 Printed 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 at this first. If you don't have access to this, check out the SqlBulkCopy class already mentioned here.

You can also learn SELECT statements with INSERT .