Using SqlBulkCopy to efficiently copy table from Access to SQL Server

SqlBulkCopy is a quick and efficient way to copy a whole table from a source to SQL Server, below shows how to copy from an Access DB to a table in SQL Server.

More info on SqlBulkCopy can be found in this link.

// Setup destination source and delete the table data if already exists

SqlConnection cnn = new SqlConnection(“Data Source=; Password=pass;User ID=user;Initial Catalog=DB” providerName=”System.Data.OleDb”);

// delete backup data
SqlCommand cmd = new SqlCommand(“DELETE FROM LPH_ProductTable”, cnn);
SqlDataReader rdr = cmd.ExecuteReader();

// Setup source data connection
String connString = (“Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + Server.MapPath(“folder1/folder2/accessfile.mdb”));

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(connString);

// Set command to get table data
OleDbCommand MDBCommand = new OleDbCommand(“Select * FROM LPH_ProductTable”, objConn);
OleDbDataReader rdr2;

// Open connection with the database.

// Read data
rdr2 = MDBCommand.ExecuteReader();

// Initializing an SqlBulkCopy object for destinaion
SqlBulkCopy sbc = new SqlBulkCopy(“Data; Password=pass;User ID=user;Initial Catalog=DB” providerName=”System.Data.OleDb”);

// Copying data to destination
sbc.DestinationTableName = “DestinationTableName”;

// Closing connection and the others