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();