如何在Access中为多列提高批量INSERT到ODBC链接表的性能? [英] How to increase performance for bulk INSERTs to ODBC linked tables in Access for multiple columns?

问题描述

此问题基于以下问题:

This question is based on on this one: How to increase performance for bulk INSERTs to ODBC linked tables in Access?

由于没有足够的分数要对该主题发表评论，因此我对上面的链接主题有进一步的询问，因此在这里创建了我的问题.

I have further queston for the linked topic above, since I do not have enough score to comment on the topic, so I created my question here.

戈德·汤普森的出色回答，他在上面的链接中提供了以下代码.

With the excellent answer from Gord Thompson, who provided the following codes in the link above.

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 Sub

Sub 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 = Nothing
End Sub

我的问题是，如果要在插入过程中包括多列怎么办?例如，除了MPO_REFERENCE之外，我还有另一列名为NEW_COLUMN的列想要插入到METER_DATA中 (来自sql server的odbc链接表)，我尝试对上述代码中的某些行进行以下修改，但失败了.抱歉，我是SQL和VB的新手，如果有人可以提供帮助，我们将不胜感激.谢谢.

My question is what if I want to include multiple columns during the insert? For example, apart from MPO_REFERENCE, I have other column named NEW_COLUMN wants to be inserted to METER_DATA (odbc linked table from sql server), and I tried following modifications on some of the lines in above codes but failed. Sorry I am new to SQL and VB, much appreciate if someone can help. Thanks.

    ...
    Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE,NEW_COLUMN FROM tblTempSmartSSP", dbOpenSnapshot)
    ...
    ...
        valueList = valueList & separator & "(" & rst!MPO_REFERENCE & "," & rst!NEW_COLUMN & ")"

    ...
    ...
    qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE,NEW_COLUMN) VALUES " & valueList

    ...

推荐答案

基本上，您了解了这个想法，并且代码是正确的.

Basically, you got the idea and the code is correct.

但是您还必须考虑其他一些事情:

But there are some other things you must consider:

1. 如果rst!NEW_COLUMN为空，则将空字符串传递给SQL命令，这将导致错误.

1. If rst!NEW_COLUMN is empty, you pass an empty string to the SQL command and it will cause an error.

例如导致SQL字符串错误:

e.g. error causing SQL-string:

INSERT INTO METER_DATA (MPO_REFERENCE,NEW_COLUMN) VALUES (3, )

在构建SQL字符串之前检查NEW_COLUMN是否为空

Check if NEW_COLUMN is empty before building the SQL string

如果NEW_COLUMN是字符串值，则必须在单引号之间设置它.看起来像这样:

If NEW_COLUMN is a string value, it must be set between single quotation marks. This would look like that:

valueList = valueList & separator & "(" & rst!MPO_REFERENCE _ & ",'" & rst!NEW_COLUMN & "')"

valueList = valueList & separator & "(" & rst!MPO_REFERENCE _ & ",'" & rst!NEW_COLUMN & "')"

第二个考虑因素也可能会解决第一个考虑因素，因为它将...VALUES (3, )...更改为...VALUES (3, '')...，从而提供了空字符串插入.

The second consideration will probably also solves the first consideration, because it changes ...VALUES (3, )... to ...VALUES (3, '')... which provides an empty string insert.

我想，MPO_REFERENCE可以在不使用单引号引起来的情况下使用，因为它是一个数字.

I assume, MPO_REFERENCE works without surrounded single quotation marks because it is a number.

这篇关于如何在Access中为多列提高批量INSERT到ODBC链接表的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！