Bulk import from MS Access and Insert into Sql Server

asp.net c# ms-access sqlbulkcopy sql-server


I want to read records from MS Access database and Insert into Sql server database, the process should be bulk insertion. I'm using asp.net/vb.net

Accepted Answer

First of all read data from Excel sheet

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/temp/") + "FileName.xlsx; Extended Properties=Excel 12.0;";

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        DbDataAdapter adapter = factory.CreateDataAdapter();
        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT ColumnNames FROM [Sheet1$]";
        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;
        selectCommand.Connection = connection;
        adapter.SelectCommand = selectCommand;
        DataTable dtbl = new DataTable();
 // Then use SQL Bulk query to insert those data

        if (dtbl.Rows.Count > 0)

 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection))
            bulkCopy.ColumnMappings.Add("ColumnName", "ColumnName");
            bulkCopy.ColumnMappings.Add("ColumnName", "ColumnName");
        bulkCopy.DestinationTableName = "DBTableName";

Popular Answer

private void Synchronize()
    SqlConnection con = new SqlConnection("Database=DesktopNotifier;Server=\\sql2008;User=common;Password=k25#ap;");
    SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM CustomerData", con);
    DataSet ds = new DataSet();
    adap.Fill(ds, "CustomerData");

    DataTable dt = new DataTable();
    dt = ds.Tables["CustomerData"];

    foreach (DataRow dr in dt.Rows)
        string File = dr["CustomerFile"].ToString();
        string desc = dr["Description"].ToString();

        string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=D:\\DesktopNotifier\\DesktopNotifier.accdb";

        OleDbConnection conn = new OleDbConnection(conString);
        string dbcommand = "insert into CustomerData (CustomerFile, Description) VALUES ('" + File + "', '" + desc + "')";
        OleDbCommand mscmd = new OleDbCommand(dbcommand, conn);


private void Configuration_Load(object sender, EventArgs e)

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why