问题描述
我在Excel中有二维数组(四列A-D，0到1000行).我想通过存储过程使用ADODB将它们插入到单个Azure SQL表中.我可以逐行执行此操作(连接字符串和过程正常工作)，但速度非常慢(插入 1000条记录最多需要30秒).有人可以帮我将整个数组作为单个插入传递，而无需遍历每一行.
I have 2 dimensional array in Excel (four columns A-D, 0 to 1000 rows). I want to insert them into a single Azure SQL table using ADODB by stored procedure . I can do this row by row (connection strings and procedures work work) but it is very slow (inserting 1000 records takes up to 30 seconds). Could someone help me to pass the whole array as a single insert without looping through each row please.
这是我的代码的示例:
公共Sub TestMultiInserts(byref MyArray()作为变体)
作为ADODB.Connection昏暗
Dim con As ADODB.Connection
将cmd转换为ADODB.Command
Dim cmd As ADODB.Command
将MyArray()设为Double
Dim MyArray() As Double
将iRow设置为整数
Dim iRow As Integer
将sConnectionString设为字符串
sConnectionString = GetConnectionString
设置con =新的ADODB.Connection
设置cmd =新的ADODB.Command
con.ConnectionString = sConnectionString
con.Open
cmd.ActiveConnection = con
cmd.CommandTimeout = 0
cmd.CommandType = adCmdStoredProc
cmd.CommandText =" usp_MyInsert_Proc"
cmd.Parameters.Append cmd.CreateParameter(" @ A，adInteger，adParamInput)
cmd.Parameters.Append cmd.CreateParameter(" @ B，adInteger，adParamInput)
cmd.Parameters.Append cmd.CreateParameter(" @ C，adDouble，adParamInput)
cmd.Parameters.Append cmd.CreateParameter(" @ D，adDouble，adParamInput)
对于iRow = LBound(MyArray，1)到UBound(MyArray，1)
cmd.Parameters("; @A).Value = MyArray(iRow，1)
cmd.Parameters("; @B).Value = MyArray(iRow，2)
cmd.Parameters("; @C'').Value = MyArray(iRow，3)
cmd.Parameters("; @D).Value = MyArray(iRow，4)
cmd.执行adExecuteNoRecords
下一个iRow
设置cmd = Nothing
con.关闭
con.Close
设置con =否
结束子
======
经过进一步研究，我使用了ADODB.RecordSet.AddNew(LockType = adLockBatchOptimistic，CursorLocation = adUseClient)，然后在循环内构造了Fields .UpdateBatch在循环外.这是一个速度上的提高，但我是 失去使用存储过程的好处(外键完整性).有谁知道如何批量执行存储过程?我正在运行Excel.VBA，所以似乎无法尝试DAO.
After further research I have used ADODB.RecordSet.AddNew (LockType = adLockBatchOptimistic,CursorLocation = adUseClient), constructing the Fields inside the loop then .UpdateBatch outside the loop. This is a speed improvement, but I am losing the benefits of using a stored procedure (foreign key integrity). Does anyone know how to execute a stored procedure in a batch? I am running Excel.VBA so cannot try DAO it seems.
======
我应该补充一点，当从目标表中删除主键索引时，带有.UpdateBatch的.AddNew方法只会更快(8秒而不是28秒).使用索引，这两种方法花费相同的28秒.我真的很想要索引.
I should add that the .AddNew method with .UpdateBatch is only faster (8 seconds rather than 28 seconds) when the primary key index is removed from the target table. With the index, both methods take the same 28 seconds. I really want the index though.
推荐答案
似乎最好的方法是将Excel表转换为ADO中的XML字符串，如本线程所述:
It appears the best method is to convert the Excel table to XML string in ADO, as discussed in this thread: Passing table of values from Excel/Access to SQL Stored Proc
本教程中还讨论了可用于将Excel数据加载到Azure SQL数据库中的所有方法:导入 数据从Excel到SQL Server或Azure SQL数据库
There is also this tutorial which discusses all the methods one could use to load Excel data into Azure SQL Database: Import data from Excel to SQL Server or Azure SQL Database
我希望这些信息能为您解决问题提供真知灼见.如果您还有其他问题，请告诉我们；如果此信息确实解决了您的问题，请将此问题标记为已回答，以帮助寻求相同解决方案的其他人.
I hope this information provides insight towards resolving your issue. Please let us know if you have additional questions and, if this information does resolve your issue, please mark this as answered so as to assist others looking for the same resolution.
此致
迈克
这篇关于Excel ADODB使用数组调用SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！