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=213.111.211.221; Password=pass;User ID=user;Initial Catalog=DB” providerName=”System.Data.OleDb”);
// delete backup data
SqlCommand cmd = new SqlCommand(“DELETE FROM LPH_ProductTable”, cnn);
cnn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
cnn.Close();
rdr.Close();// 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.
objConn.Open();// Read data
rdr2 = MDBCommand.ExecuteReader();// Initializing an SqlBulkCopy object for destinaion
SqlBulkCopy sbc = new SqlBulkCopy(“Data Source=xxx.xxx.xxx.xxx; Password=pass;User ID=user;Initial Catalog=DB” providerName=”System.Data.OleDb”);// Copying data to destination
sbc.DestinationTableName = “DestinationTableName”;
sbc.WriteToServer(rdr2);// Closing connection and the others
sbc.Close();
rdr2.Close();
objConn.Close();