How to perform the bulk update of the SQL Server table by the MS ACCESS table

advertisements

I need to update several millions records in SQL Server from MS ACCESS table. I use MS ACCESS as a frontend and SQL as linked tables. So as I understand I need to create pass-through query to execute something like this:

UPDATE SQLtbl SET SQLtbl.col1 = MDBtbl.Col1 FROM SQLtbl INNER JOIN MDBtbl ON SQLtbl.ID = MDBtbl.ID WHERE SQLtbl.col1 != MDBtbl.Col1

It works extremely slow so I need to convert int somehow to BULK UPDATE Please advice, Thank you


Similar to a recent related question here, this is another one of those cases where treating ODBC linked tables just like native Access tables can result in slow performance.

For two identical Access tables named [SQLtbl] (ODBC linked to SQL Server) and [MDBtbl] (native Access) with 9999 rows each, the following code took around 5.5 minutes to execute:

Sub UpdateViaJoin()
    Dim con As ADODB.Connection
    Dim t0 As Single

    Set con = CurrentProject.Connection
    con.CommandTimeout = 0
    t0 = Timer
    con.Execute _
            "UPDATE " & _
                "SQLtbl INNER JOIN MDBtbl " & _
                    "ON SQLtbl.ID = MDBtbl.ID " & _
            "SET SQLtbl.Col1 = MDBtbl.Col1"
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set con = Nothing
End Sub

To see if the JOIN itself was a problem, I ran the following which took just over 5 minutes to complete:

Sub UbdateViaDLookup()
    Dim cdb As DAO.Database
    Dim t0 As Single

    Set cdb = CurrentDb
    t0 = Timer
    cdb.Execute _
            "UPDATE SQLtbl SET Col1 = DLookup(""Col1"", ""MDBtbl"", ""ID="" & ID)"
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set cdb = Nothing
End Sub

On the other hand, the following code that uses a pass-through query and a native T-SQL prepared statement consistently ran in under 2 seconds (that is, more than 100 times faster):

Sub UpdateViaPassThroughQuery()
    Dim cdb As DAO.Database, rst As DAO.Recordset, qdf As DAO.QueryDef
    Dim SQL As String, statementHandle As Long, i As Long, updateList As String
    Dim t0 As Single

    Set cdb = CurrentDb
    t0 = Timer

    SQL = "SET NOCOUNT ON;"
    SQL = SQL & "DECLARE @statementHandle int;"
    SQL = SQL & "EXEC sp_prepare @statementHandle OUTPUT, N'@P1 nvarchar(50), @P2 int', N'UPDATE SQLtbl SET [email protected] WHERE [email protected]';"
    SQL = SQL & "SELECT @statementHandle;"
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = cdb.TableDefs("SQLtbl").Connect
    qdf.SQL = SQL
    qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    statementHandle = rst(0).Value
    rst.Close

    Set rst = cdb.OpenRecordset("SELECT ID, Col1 FROM MDBtbl", dbOpenSnapshot)
    i = 0
    updateList = ""
    Do Until rst.EOF
        i = i + 1
        updateList = updateList & "EXEC sp_execute " & statementHandle & ", N'" & Replace(rst!Col1, "'", "''") & "', " & rst!id & ";"
        If i = 1000 Then
            qdf.SQL = updateList
            qdf.ReturnsRecords = False
            qdf.Execute
            i = 0
            updateList = ""
        End If
        rst.MoveNext
    Loop
    If i > 0 Then
        qdf.SQL = updateList
        qdf.ReturnsRecords = False
        qdf.Execute
    End If
    rst.Close
    Set rst = Nothing

    qdf.SQL = "EXEC sp_unprepare " & statementHandle & ";"
    qdf.ReturnsRecords = False
    qdf.Execute
    Set qdf = Nothing
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set cdb = Nothing
End Sub

Edit

To tweak the above code to handle Nulls you would need to update the line ...

updateList = updateList & "EXEC sp_execute " & statementHandle & ", N'" & Replace(rst!Col1, "'", "''") & "', " & rst!id & ";"

... to ...

updateList = updateList & "EXEC sp_execute " & statementHandle & ", " & _
        FormatArgForPrepStmt(rst!Col1) & ", " & _
        rst!id & ";"

... and add a little formatting function something like this:

Private Function FormatArgForPrepStmt(item As Variant) As String
    If IsNull(item) Then
        FormatArgForPrepStmt = "NULL"
    Else
        Select Case VarType(item)
            Case vbString
                FormatArgForPrepStmt = "N'" & Replace(item, "'", "''") & "'"
            Case vbDate
                FormatArgForPrepStmt = "N'" & Format(item, "yyyy-mm-dd Hh:Nn:Ss") & "'"
            Case Else
                FormatArgForPrepStmt = CStr(item)
        End Select
    End If
End Function