How to increase performance for bulk INSERTs to ODBC linked tables in Access?

This situation is not uncommon when dealing with bulk INSERTs to ODBC linked tables in Access. In the case of the following Access query

INSERT INTO METER_DATA (MPO_REFERENCE) SELECT MPO_REFERENCE FROM tblTempSmartSSP

where [METER_DATA] is an ODBC linked table and [tblTempSmartSSP] is a local (native) Access table, ODBC is somewhat limited in how clever it can be because it has to be able to accommodate a wide range of target databases whose capabilities may vary greatly. Unfortunately, it often means that despite the single Access SQL statement what actually gets sent to the remote (linked) database is a separate INSERT (or equivalent) for each row in the local table. Understandably, that can prove to be very slow if the local table contains a large number of rows.

Option 1: Native bulk inserts to the remote database

All databases have one or more native mechanisms for the bulk loading of data: Microsoft SQL Server has "bcp" and BULK INSERT, and Oracle has "SQL*Loader". These mechanisms are optimized for bulk operations and will usually offer significant speed advantages. In fact, if the data needs to be imported into Access and "massaged" before being transferred to the remote database it can still be faster to dump the modified data back out to a text file and then bulk import it into the remote database.

Option 2: Using a pass-through query in Access

If the bulk import mechanisms are not a feasible option, then another possibility is to build one or more pass-through queries in Access to upload the data using INSERT statements that can insert more than one row at a time.

For example, if the remote database was SQL Server (2008 or later) then we could run an Access pass-through (T-SQL) query like this

INSERT INTO METER_DATA (MPO_REFERENCE) VALUES (1), (2), (3)

to insert three rows with one INSERT statement.

According to an answer to another earlier question here the corresponding syntax for Oracle would be

INSERT ALL    INTO METER_DATA (MPO_REFERENCE) VALUES (1)    INTO METER_DATA (MPO_REFERENCE) VALUES (2)    INTO METER_DATA (MPO_REFERENCE) VALUES (3)SELECT * FROM DUAL;

I tested this approach with SQL Server (as I don't have access to an Oracle database) using a native [tblTempSmartSSP] table with 10,000 rows. The code ...

Sub LinkedTableTest()    Dim cdb As DAO.Database    Dim t0 As Single    t0 = Timer    Set cdb = CurrentDb    cdb.Execute _            "INSERT INTO METER_DATA (MPO_REFERENCE) " & _            "SELECT MPO_REFERENCE FROM tblTempSmartSSP", _            dbFailOnError    Set cdb = Nothing    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."End Sub

... took approximately 100 seconds to execute in my test environment.

By contrast the following code, which builds multi-row INSERTs as described above (using what Microsoft calls a Table Value Constructor) ...

Sub PtqTest()    Dim cdb As DAO.Database, rst As DAO.Recordset    Dim t0 As Single, i As Long, valueList As String, separator As String    t0 = Timer    Set cdb = CurrentDb    Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE FROM tblTempSmartSSP", dbOpenSnapshot)    i = 0    valueList = ""    separator = ""    Do Until rst.EOF        i = i + 1        valueList = valueList & separator & "(" & rst!MPO_REFERENCE & ")"        If i = 1 Then            separator = ","        End If        If i = 1000 Then            SendInsert valueList            i = 0            valueList = ""            separator = ""        End If        rst.MoveNext    Loop    If i > 0 Then        SendInsert valueList    End If    rst.Close    Set rst = Nothing    Set cdb = Nothing    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."End SubSub SendInsert(valueList As String)    Dim cdb As DAO.Database, qdf As DAO.QueryDef    Set cdb = CurrentDb    Set qdf = cdb.CreateQueryDef("")    qdf.Connect = cdb.TableDefs("METER_DATA").Connect    qdf.ReturnsRecords = False    qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE) VALUES " & valueList    qdf.Execute dbFailOnError    Set qdf = Nothing    Set cdb = NothingEnd Sub

... took between 1 and 2 seconds to produce the same results.

(T-SQL Table Value Constructors are limited to inserting 1000 rows at a time, so the above code is a bit more complicated than it would be otherwise.)

Do you have to import all the columns? Maybe you want to leave out empty columns, if any; and also columns which are not absolutely necessary for business purposes

Sorry, I forgot to include the code:

Option Compare DatabaseOption ExplicitPublic Function Run_Safe_SQL(strSQL)On Error GoTo Error_HandlerDim db As DAO.Database   Set db = CurrentDb()   db.Execute strSQL, dbFailOnError   DBEngine.Idle dbRefreshCache'   DoEventsExit_Here:   'Cleanup   Set db = Nothing   strSQL = ""   Exit FunctionError_Handler:    MsgBox Err.Description & " " & Err.NumberEnd Function