从VBA中的数组中批量插入到sql中 [英] Bulk insert into sql from an array in VBA

问题描述

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

I am trying to build a button in excel that would upload the selected region into a table in the sql server. The first row would be automatically treated as the column headers.

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

How to go on about this? What I want is simplicity & super-fast uploading.

这是我的想法---

我会选择所选区域，然后保存为.txt文件，然后在其上运行大量的insert语句。有没有一种直接批量插入所选区域的方法（可能首先将它放在一个变体数组中），而不先将其保存为.txt文件？

I would take the selected region and then save it as a .txt file and then run a bulk insert statement on it. Is there a method to directly bulk insert the selected region (maybe take it in a variant array first), without first saving it as a .txt file?

另外，如果有一个更有效的方法，然后提出。

Also, if there is a more efficient method, then do propose.

推荐答案

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

I would start with a simple ADO connection to the server and execute a bunch of INSERT INTO statements and see how it is performance-wise. If that doesn't work, then you can look at creating a text file with the same INSERT INTO statements. That may be faster, I really don't know. Here's some code to convert a range into the SQL statements you need

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

以下是我投掷的假数据的输出情况：

Here's what the output would look like with some fake data I threw together:

?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连接的执行调用或将其写入文本文件。我无法想象任何其他事情都会比使用SQL语句更快。例如，您可以在循环访问可更新记录集时更新.Fields.Value属性，但是没有办法会比这更快。

You could put this into an Execute call on your ADO connection or write it out to a text file. I can't imagine that anything else would be faster than using SQL statements. For instance, you could update the .Fields.Value properties as you loop through an updatable recordset, but there's no way that's going to be faster than this.

重新做一百万行，但VBA中没有什么会很快。所以请记住这一点。

If you're doing a million rows, nothing in VBA will be fast, though. So keep that in mind.

这篇关于从VBA中的数组中批量插入到sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！