Best method for transferring data between Access database to SQL database

Question

I have recently been assigned a task to build a c# windows forms app. It needs to read large amounts of data from an Access database and transfer to a SQL database, deleting old SQL data before transferring.

I have never done anything like this before and was wondering what the best approach would be ?

Also, do I need to read each Access table individually and save them in different datasets e.g dataset1, dataset2 for each table ?

Any help is greatly appreciated.

Thanks

Answer 1

If you decide to implement your own transfer mechanism in your C# application and you really are working with large amounts of data then be careful to choose a method that does not end up inserting the data into SQL Server row-by-row. Clearly an approach like

OdbcDataReader rdrAccess = cmdAccess.ExecuteReader();
while (rdrAccess.Read())
{
    // insert a row into SQL Server
}

will almost certainly be slow, but even a single Access SQL statement like

using (var cmd = new OdbcCommand())
{
    cmd.Connection = connAccess;
    cmd.CommandText = 
        $"INSERT INTO [ODBC;{connStrSqlOdbc}].[BulkTable] (id, txtcol) " +
        $"SELECT ID, TextField AS txtcol FROM Table1 WHERE ID<=1000";
    cmd.ExecuteNonQuery();
}

will end up sending 1000 individual T-SQL statements of the form

exec sp_executesql N'INSERT INTO  "dbo"."BulkTable"  ("id","txtcol") VALUES (@P1,@P2)',N'@P1 int,@P2 nvarchar(50)',1,N'record000000'
exec sp_executesql N'INSERT INTO  "dbo"."BulkTable"  ("id","txtcol") VALUES (@P1,@P2)',N'@P1 int,@P2 nvarchar(50)',2,N'record000001'
exec sp_executesql N'INSERT INTO  "dbo"."BulkTable"  ("id","txtcol") VALUES (@P1,@P2)',N'@P1 int,@P2 nvarchar(50)',3,N'record000002'
...
exec sp_executesql N'INSERT INTO  "dbo"."BulkTable"  ("id","txtcol") VALUES (@P1,@P2)',N'@P1 int,@P2 nvarchar(50)',1000,N'record000999'

to the SQL Server.

Probably your best bet would be to use System.Data.SqlClient and a SqlBulkCopy object, like so

using (var cmd = new OdbcCommand())
{
    cmd.Connection = connAccess;
    cmd.CommandText = "SELECT ID, TextField AS txtcol FROM Table1 WHERE ID<=1000";
    using (OdbcDataReader rdr = cmd.ExecuteReader())
    {
        using (var sbc = new SqlBulkCopy(connStrSqlClient))
        {
            sbc.DestinationTableName = "BulkTable";
            sbc.WriteToServer(rdr);
        }
    }
}

On my test network, the SqlBulkCopy approach consistently finished in less than one-tenth of the time that the INSERT INTO ... SELECT method took.