none
Bulk insert in MS ACCESS RRS feed

  • Question

  • Hi All,

    Does anyone know, how to insert bulk data into to MS Access tables. In my case I have created One database(.mdb file) and I want to create 8 tables. every table will contains 10,000 rows at least. So I have use below code to save into the database... but somehow it takes time to perform the operation. in my case it takes around 1 minute. 

             using (var insertCommand = new OleDbCommand(insertQuery, conn))
                    {
                        for (int row = 0; row < datTable.Rows.Count; row++)
                        {
                            for (int col = 0; col < datTable.Columns.Count; col++)
                            {
                                parameters[col].Value = datTable.Rows[row][col];
                            }
                            insertCommand.Parameters.Clear();
                            insertCommand.Parameters.AddRange(parameters.ToArray());
                            insertCommand.ExecuteNonQuery();
                        }
                    }

    Anyone know how can I optimize above code for better performance. or can I use bulk insert query if available in ms access?

    -Ajit

    Tuesday, July 9, 2013 5:14 AM

Answers

  • Hi Ajit,

    instead of clearing and (re)creating the Parameters inside the loop, add the them once and call Prepare. In the loop supply only the values:

    using (var insertCommand = new OleDbCommand(insertQuery, conn))
    {
    	insertCommand.Parameters.AddRange(parameters.ToArray());
    	insertCommand.Prepare();
    
    	for (int row = 0; row < datTable.Rows.Count; row++)
    	{
    		for (int col = 0; col < datTable.Columns.Count; col++)
    		{
    			insertCommand.Parameters[col].Value = datTable.Rows[row][col];
    		}
    		insertCommand.ExecuteNonQuery();
    	}
    }

    But a bulk insert doesn't exists for Access / Jet. The fastest approach would be a isam access, but that isn't implemented by the OleDb Client and would require ADODB or DAO.

    Regards, Elmar


    Tuesday, July 9, 2013 2:11 PM
  • There is no bulk insert into an Access database when using a DataTable as a source. You could try merging the DataTable into another and then performing an Update, but I'm not sure how much faster that would be.

    http://www.knowdotnet.com/articles/datasetmerge.html


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 9, 2013 5:09 PM
  • Batch/bulk operation are only supported for NET data providers for SQL Server (System.Data.SqlClient) and Oracle (System.Data.OracleClient).

    You can use DAO as an alternative to improve performance as It has been observed that, DAO connection is roughly 10 - 30x faster than ODBC or OLEDB provider as it sits closure to Ms-Access database engine. Refer:

    http://stackoverflow.com/questions/15788441/ms-access-batch-update-via-ado-net-and-com-interoperability


    Lingaraj Mishra

    Tuesday, July 9, 2013 8:11 PM

All replies

  • Hi All,

    Does anyone know, how to insert bulk data into to MS Access tables. In my case I have created One database(.mdb file) and I want to create 8 tables. every table will contains 10,000 rows at least. So I have use below code to save into the database... but somehow it takes time to perform the operation. in my case it takes around 1 minute. 

             using (var insertCommand = new OleDbCommand(insertQuery, conn))
                    {
                        for (int row = 0; row < datTable.Rows.Count; row++)
                        {
                            for (int col = 0; col < datTable.Columns.Count; col++)
                            {
                                parameters[col].Value = datTable.Rows[row][col];
                            }
                            insertCommand.Parameters.Clear();
                            insertCommand.Parameters.AddRange(parameters.ToArray());
                            insertCommand.ExecuteNonQuery();
                        }
                    }

    Anyone know how can I optimize above code for better performance. or can I use bulk insert query if available in ms access?

    -Ajit

    • Merged by Bob Shen Wednesday, July 10, 2013 6:13 AM duplicate
    Tuesday, July 9, 2013 8:54 AM
  • You are running one query for each row so that is going to take a while.  You should send the entire table to ADO.NET and let it do the bulk update rather than building up the query yourself.  Since you only provided the code you're using to insert data I cannot tell how you got the schema original or what data access technology you're using.  Therefore I'll direct you to this post on how to do it using the traditional ADO.NET approach.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Tuesday, July 9, 2013 1:57 PM
  • Hi Ajit,

    instead of clearing and (re)creating the Parameters inside the loop, add the them once and call Prepare. In the loop supply only the values:

    using (var insertCommand = new OleDbCommand(insertQuery, conn))
    {
    	insertCommand.Parameters.AddRange(parameters.ToArray());
    	insertCommand.Prepare();
    
    	for (int row = 0; row < datTable.Rows.Count; row++)
    	{
    		for (int col = 0; col < datTable.Columns.Count; col++)
    		{
    			insertCommand.Parameters[col].Value = datTable.Rows[row][col];
    		}
    		insertCommand.ExecuteNonQuery();
    	}
    }

    But a bulk insert doesn't exists for Access / Jet. The fastest approach would be a isam access, but that isn't implemented by the OleDb Client and would require ADODB or DAO.

    Regards, Elmar


    Tuesday, July 9, 2013 2:11 PM
  • Hi Michael,

    Thanks for the reply. After profiling the function I come to know that ExecuteNonQueryis causing performance issues as that in in loop. For more information I am giving the entire function:

    private void SaveDataTable(DataTable datTable, OleDbConnection conn)
            {
                try
                {
                    if (conn.State.Equals(ConnectionState.Closed))
                    {
                        conn.Open();
                    }
                   
                    string insertQuery = string.Empty;
                    string values = string.Empty;

                    var parameters = new List<OleDbParameter>();

                    for (int index = 0; index < datTable.Columns.Count; index++)
                    {
                        insertQuery += _OpenSquare + datTable.Columns[index].ColumnName + _CloseSquare;
                        values += _At + datTable.Columns[index].ColumnName;
                        if (index < datTable.Columns.Count - 1)
                        {
                            insertQuery += _Comma;
                            values += _Comma;
                        }

                        OleDbType dataType = GetDataTypeForColumn(datTable.Columns[index].ColumnName, datTable.TableName);
                        parameters.Add(new OleDbParameter(_At + datTable.Columns[index].ColumnName, dataType, 100,
                                                          datTable.Columns[index].ColumnName));
                    }

                    insertQuery = _Insert + datTable.TableName + _Open1 + insertQuery + _Close;
                    insertQuery = insertQuery + _Value + values + _Close;
                    using (var insertCommand = new OleDbCommand(insertQuery, conn))
                    {
                        for (int row = 0; row < datTable.Rows.Count; row++)
                        {
                            for (int col = 0; col < datTable.Columns.Count; col++)
                            {
                                parameters[col].Value = datTable.Rows[row][col];
                            }

                            insertCommand.Parameters.Clear();
                            insertCommand.Parameters.AddRange(parameters.ToArray());
                            insertCommand.ExecuteNonQuery();
                        }
                    }

                    if (conn.State.Equals(ConnectionState.Open))
                    {
                        conn.Close();
                    }
                }

                finally
                {
                    if (conn.State.Equals(ConnectionState.Open))
                    {
                        conn.Close();
                    }
                }
            }

    I call this function using SaveDataTable(datatable, conn).  I use same function for writing all 11 tables in DB.

    Let me know if you gets anything to improve the performance.

    -Ajit

    Tuesday, July 9, 2013 3:00 PM
  • There is no bulk insert into an Access database when using a DataTable as a source. You could try merging the DataTable into another and then performing an Update, but I'm not sure how much faster that would be.

    http://www.knowdotnet.com/articles/datasetmerge.html


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 9, 2013 5:09 PM
  • Batch/bulk operation are only supported for NET data providers for SQL Server (System.Data.SqlClient) and Oracle (System.Data.OracleClient).

    You can use DAO as an alternative to improve performance as It has been observed that, DAO connection is roughly 10 - 30x faster than ODBC or OLEDB provider as it sits closure to Ms-Access database engine. Refer:

    http://stackoverflow.com/questions/15788441/ms-access-batch-update-via-ado-net-and-com-interoperability


    Lingaraj Mishra

    Tuesday, July 9, 2013 8:11 PM
  • Hi,

    Thanks for the reply. I have already tried with DAO, and that is much faster! However the issue is we need to have external references, as that's comes with VS 2010 and not with .NET Framework 4.0. If I want to give these to customer then how I can manage that?

    -Ajit

    Wednesday, July 10, 2013 4:18 AM
  • Are you referring DAO 3.6 Object Library? If so it is part of Office 200X installation. Hence you do not need to provide it externally.

    DAO is completely obsolete, hence using it is not recommended unless until you are looking for a drastic performance improvements.


    Lingaraj Mishra

    Monday, July 15, 2013 5:11 PM
  • Please do not reuse old questions. Post your own question in the forums.
    Monday, July 10, 2017 1:45 PM
  • First try to save data to CSV file and then import this CSV file into Access in code. It works FAST.

    Row-by-row inserting in my code worked with speed 100 records/sec. But when I have implemented importing of CSV file, 1 milllon of records has been imported in less than minute.

    See

    https://stackoverflow.com/questions/19208603/insert-into-access-table-from-csv-file-via-c-sharp-code

     using (var con = new OdbcConnection())
                {
                    con.ConnectionString = myConnectionString;
                    con.Open();
    
                    using (var cmd = new OdbcCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandType = System.Data.CommandType.Text;
                        cmd.CommandText =
                                @"INSERT INTO Items " +
                                @"SELECT * FROM [Text;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;ACCDB=YES;Database=C:\Users\Public].[Items#csv];";
                        cmd.ExecuteNonQuery();
                    }
                    con.Close();
                }

    Thursday, December 21, 2017 3:25 PM
  • PS. My SQL for CSV connection looks next (dataFilePath = "c:\aaa\bbb\myfile.csv")

    sb.AppendFormat(" FROM [Text;FMT=Delimited;HDR=YES;Database={0}].[{1}]", IO.Path.GetDirectoryName(dataFilePath), Path.GetFileName(dataFilePath))

    Thursday, December 21, 2017 3:29 PM