sql - 从VBA中的数组批量插入sql

Question

我正在尝试在 excel 中构建一个按钮，将所选区域上传到 sql server 中的表中。第一行将被自动视为列标题。

如何继续这件事？我想要的是简单和超快速上传。

这是我的想法---

我会选择选定的区域，然后将其保存为 .txt 文件，然后在其上运行批量插入语句。有没有一种方法可以直接批量插入所选区域(可能先将其放入变体数组中)，而无需先将其保存为 .txt 文件？

另外，如果有更有效的方法，请提出建议。

Answer 1

我将从与服务器的简单 ADO 连接开始，然后执行一堆 INSERT INTO 语句，看看它的性能如何。如果这不起作用，那么您可以查看使用相同的 INSERT INTO 语句创建一个文本文件。那可能更快，我真的不知道。这是一些将范围转换为您需要的 SQL 语句的代码

Function RangeToInsert(rRng As Range) As String

    Dim vaData As Variant
    Dim i As Long, j As Long
    Dim aReturn() As String
    Dim aCols() As String
    Dim aVals() As Variant

    Const sINSERT As String = "INSERT INTO MyTable "
    Const sVAL As String = " VALUES "

    'Read in data
    vaData = rRng.Value

    'Create arrays
    ReDim aReturn(1 To UBound(vaData))
    ReDim aCols(1 To UBound(vaData, 2))
    ReDim aVals(1 To UBound(vaData, 2))

    'Fill column name array from first row
    For j = LBound(vaData, 2) To UBound(vaData, 2)
        aCols(j) = vaData(1, j)
    Next j

    'Go through the rest of the rows
    For i = LBound(vaData, 1) + 1 To UBound(vaData, 1)

        'Fill a temporary array
        For j = LBound(vaData, 2) To UBound(vaData, 2)
            aVals(j) = vaData(i, j)
        Next j

        'Build the string into the main array
        aReturn(i) = sINSERT & "(" & Join(aCols, ",") & ")" & sVAL & "(" & Join(aVals, ",") & ");"
    Next i

    RangeToInsert = Join(aReturn, vbNewLine)

End Function

以下是我拼凑的一些假数据的输出结果:

?rangetoinsert(selection)

INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (97,100,53,27,14,53,94,43);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (21,96,69,60,70,8,35,54);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (8,12,78,38,82,67,41,53);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (15,32,89,100,61,78,16,37);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (46,37,75,66,66,93,19,45);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (82,30,56,10,99,2,1,29);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (98,39,98,96,95,60,16,73);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (65,79,69,70,74,86,15,59);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (30,37,12,57,86,94,6,53);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (1,20,91,65,20,26,96,57);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (81,7,68,65,56,27,81,80);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (94,42,43,33,46,82,44,24);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (88,48,34,83,58,64,36,90);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (51,28,43,70,12,29,96,27);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (62,54,76,86,92,41,40,84);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (11,21,32,30,65,6,22,75);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (44,72,38,73,44,93,4,16);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (39,90,96,16,9,6,17,50);

您可以将其放入 ADO 连接上的 Execute 调用中，或将其写入文本文件。我无法想象还有什么比使用 SQL 语句更快。例如，您可以在循环通过可更新记录集时更新 .Fields.Value 属性，但没有比这更快的方法了。

但是，如果您正在执行一百万行，则 VBA 中的任何内容都不会很快。所以记住这一点。