在VBA中从数组大容量插入到sql中

Question

我正在尝试建立一个在excel中的按钮，将上载到sql服务器中的表选定的地区。第一行将被自动视为列标题。

如何继续这方面的工作呢？我想要的是简单和超快速的上传。

这是我的想法

我将获取所选区域，然后将其保存为.txt文件，然后对其运行bulk insert语句。有没有一种方法可以直接批量插入选定的区域(可能首先在变量数组中获取它)，而不需要先将其保存为.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中没有任何东西是快速的。所以请记住这一点。

Answer 2

首先，您需要编写一个函数(我的函数名为insert form)。

Function insertForm(givenRange As Range) As String
--Convert each row to a string format of values
--Each value needs to have all instances of ' replaced with ''
--Each value needs to be wrapped in '
--Use Join with comma(,) to join all the values and put brackets around the whole thing
--This is the insert form for one row.
--You can get the other rows by writing the result of the function recursively
--If givenRange.Rows.Count > 1 Then
    --insertForm = insertForm & ", " & insertForm(givenRange.Offset(1, 0).Resize(givenRange.Rows.Count - 1, givenRange.Columns.Count))
--End If
End Function

因此，如果我选择4列，其中2行包含

1 2 3 4
5 6 7 8

此函数创建('1'，'2'，'3'，'4')，('5'，'6'，'7'，'8')然后我可以在INSERT SQL语句中使用它，如下所示：

插入到范围值InsertForm( TableName )