vba 从VBA中的数组批量插入sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20837554/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk insert into sql from an array in VBA
提问by Black Dagger
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.
我正在尝试在 excel 中构建一个按钮，将所选区域上传到 sql server 中的表中。第一行将被自动视为列标题。
How to go on about this? What I want is simplicity & super-fast uploading.
这件事怎么办？我想要的是简单和超快速上传。
This is my idea ---
这是我的想法——
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?
我会选择选定的区域，然后将其保存为 .txt 文件，然后在其上运行批量插入语句。有没有一种方法可以直接批量插入所选区域（可能先将其放入变体数组中），而无需先将其保存为 .txt 文件？
Also, if there is a more efficient method, then do propose.
另外，如果有更有效的方法，那么请提出。
回答by Dick Kusleika
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
我将从与服务器的简单 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
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);
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.
您可以将其放入 ADO 连接上的 Execute 调用中或将其写出到文本文件中。我无法想象还有什么比使用 SQL 语句更快。例如，您可以在循环更新可更新记录集时更新 .Fields.Value 属性，但没有比这更快的方法了。
If you're doing a million rows, nothing in VBA will be fast, though. So keep that in mind.
但是，如果您要处理一百万行，则 VBA 中的任何内容都不会很快。所以记住这一点。
回答by Martin
First you need to write a function (mine is called 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
So if I select 4 columns with 2 rows that contain
因此，如果我选择 4 列，其中 2 行包含
1 2 3 4
5 6 7 8
This function creates ('1','2','3','4'),('5','6','7','8') I can then use this in the INSERT SQL statement as follows:
这个函数创建 ('1','2','3','4'),('5','6','7','8') 然后我可以在 INSERT SQL 语句中使用它，如下所示：
INSERT INTO TableName VALUES InsertForm(Range)
INSERT INTO TableName VALUES InsertForm(Range)
