E
Einat Lapid
Hi,
I was looking for few weeks now for the optimal way to upload data from
Access to SQL Server. I got few suggestions and all fell short for some
reason.
One idea was to use SSIS which I could not because I needed to support SQL
Express. Other idea was to create a linked table to the SQL Server
destination
and to do "insert into <linked_table> select * from access_source_table".
This method worked fine but fails with wierd errors when the source table
was large around 1000000 rows). Even few Access MVP could not come with
something better then to read each row from Access and to generate
parametrised insert for that row in SQL Server. That worked fine but of
course was relativly slow.
Then I read in some place on a different method - use
System.Data.OleDb.OleDbConnection with Jet oledb provider to create a
DataReader
to the Jet source and then to feed the System.Data.SqlClient.SqlBulkCopy
with this DataReader and it just works, simple and fast ! The Sample code is
below.
I just wonder how come nobody was able to point me to this kind of simple
solutiion ? Is there any caveats and hidden problems here ?
static void TransferTableData(
String AccessSourcedb,
String DestinationConnectionStr,
String TableName
)
{
int Start = GetTickCount();
//Connection to the destination
//
SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);
DestConnection.Open();
SqlCommand Cmd = DestConnection.CreateCommand();
//Connection to source
//
OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);
OledbSrcConnection.Open();
// Reader to source
OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +
TableName);
OleDbCommand.Connection = OledbSrcConnection;
OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();
//bulk upload to destination //
SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);
bulkCopy.BulkCopyTimeout = 100000000;
bulkCopy.DestinationTableName = TableName;
bulkCopy.WriteToServer(OleDbDataReader);
int End = GetTickCount();
Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);
}
[DllImportAttribute("kernel32.dll", SetLastError = true)]
private static extern int GetTickCount();
I was looking for few weeks now for the optimal way to upload data from
Access to SQL Server. I got few suggestions and all fell short for some
reason.
One idea was to use SSIS which I could not because I needed to support SQL
Express. Other idea was to create a linked table to the SQL Server
destination
and to do "insert into <linked_table> select * from access_source_table".
This method worked fine but fails with wierd errors when the source table
was large around 1000000 rows). Even few Access MVP could not come with
something better then to read each row from Access and to generate
parametrised insert for that row in SQL Server. That worked fine but of
course was relativly slow.
Then I read in some place on a different method - use
System.Data.OleDb.OleDbConnection with Jet oledb provider to create a
DataReader
to the Jet source and then to feed the System.Data.SqlClient.SqlBulkCopy
with this DataReader and it just works, simple and fast ! The Sample code is
below.
I just wonder how come nobody was able to point me to this kind of simple
solutiion ? Is there any caveats and hidden problems here ?
static void TransferTableData(
String AccessSourcedb,
String DestinationConnectionStr,
String TableName
)
{
int Start = GetTickCount();
//Connection to the destination
//
SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);
DestConnection.Open();
SqlCommand Cmd = DestConnection.CreateCommand();
//Connection to source
//
OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);
OledbSrcConnection.Open();
// Reader to source
OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +
TableName);
OleDbCommand.Connection = OledbSrcConnection;
OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();
//bulk upload to destination //
SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);
bulkCopy.BulkCopyTimeout = 100000000;
bulkCopy.DestinationTableName = TableName;
bulkCopy.WriteToServer(OleDbDataReader);
int End = GetTickCount();
Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);
}
[DllImportAttribute("kernel32.dll", SetLastError = true)]
private static extern int GetTickCount();