So führen Sie das Bulk-Update der SQL server-Tabelle nach der MS ACCESS-Tabelle aus

Ich muss mehrere Millionen datasätze in SQL server aus MS ACCESS-Tabelle aktualisieren. Ich benutze MS ACCESS als Frontend und SQL als verknüpfte Tabellen. So wie ich verstehe, muss ich pass-through-Abfrage erstellen, um so etwas auszuführen:

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

Es funktioniert extrem langsam, also muss ich int irgendwie in BULK UPDATE umwandeln Bitte Rat, danke

Ähnlich wie bei einer kürzlich verwandten Frage hier ist dies ein weiterer Fall, bei dem die Behandlung von ODBC-verknüpften Tabellen wie native Access-Tabellen zu langsamer performance führen kann.

Für zwei identische Zugriffstabellen mit dem Namen [SQLtbl] (ODBC verknüpft mit SQL server) und [MDBtbl] (native Access) mit jeweils 9999 Zeilen hat der folgende Code etwa 5,5 Minuten ausgeführt:

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 

Um zu sehen, ob das JOIN selbst ein Problem war, lief ich das folgende, das knapp über 5 Minuten dauerte:

 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 

Auf der anderen Seite lief der folgende Code, der eine Pass-Through-Abfrage und eine native T-SQL-vorbereitete statement verwendet, konsequent in weniger als 2 Sekunden (das heißt mehr als 100 mal schneller):

 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 Col1=@P1 WHERE ID=@P2';" 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 

Bearbeiten

Um den obigen Code zu umgehen, um Nulls zu behandeln, müssten Sie die Zeile aktualisieren …

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

… nach …

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

… und füge eine kleine Formatierungsfunktion so etwas hinzu:

 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 

Das Plakat fragt eindeutig:

 “I understand I need to crate pass-though queries”. 

Also müssen wir uns ansprechen, wie man eine Pass-even-Abfrage erstellt und ausführt.

Die einfache Lösung ist, um so Ihre Abfrage als pass-though zu speichern. In der Tat können Sie "ändern" Ihre bestehende Abfrage zu pass-though.

Bringen Sie Ihre vorhandene Abfrage in Design-view, und treffen Sie die Pass-Gedanken-button.

ZB das:

Bildbeschreibung hier eingeben

Wenn Sie Ihre Abfrage als pass-though speichern, dann führen Sie server-Seite t-sql.

Beachten Sie, dass die Pass-Through-button getroffen wird, dann wird auch das Eigenschaftsblatt angezeigt. Ich umkreiste die Pass-button und die ODBC-Einstellung im Eigenschaftsblatt. In der Tat kannst du die Odbc-Verbindungszeichenfolge aus einer verknüpften Tabelle über cut + paste cOPY.

Also: Es besteht keine Notwendigkeit, Verbindungszeichenfolgen in Code einzurichten. Es gibt keine Notwendigkeit, VBA-Code zu schreiben. Es gibt in der Tat KLEINER WENN JEDER MUSS, um noch einmal schreiben die sql, sollte es funktionieren, wie Sie als native t-sql-Code haben.

So erreicht man hier eher wünschenswerte Ziele:

Sie können die EXISTING-Abfrage erneut verwenden.

Du musst die Abfrage nicht neu berechnen.

Du musst kein GROSSES Bündel Code schreiben und schreiben.

Sie müssen nicht einmal wissen, VBA

Sie führen keine WHOLE NEW ADO objectmodellbibliothek in eine bestehende Anwendung ein. Es ist unwahrscheinlich, dass die vorhandene Anwendung die ADO-objectbibliothek verwendete. Sie müssen nun diese WHOLE neue objectbibliothek in Ihre bestehende Anwendung einführen. Das Hinzufügen dieses neuen reference- und VBA-Codes wird somit ein erhebliches Potenzial schaffen, um Bugs in die bestehende Codebasis zu brechen und einzuführen. Dies ist EXPECIELL der Fall, wenn der vorhandene Code keine DAO-Recordsets qualifiziert hat.

Ich denke, um ein Pass-obwohl Update laufen, es ist viel einfacher und risikofrei, einfach nur eine Pass-obwohl-Abfrage verwenden und einfach auf das Band klicken dann die Einführung der ganzen neuen ADO-reference und object libriares und DANN stellen eine Reihe von VBA ADO-Code, um einfach eine einfache Update-Abfrage auszuführen.