将Excel表格数据传输到SQL 2008R2的最快方法
有没有人知道在不使用外部实用程序（即bcp）的情况下从SQL 2008中获取数据和Excel表（VBA数组）的最快方法？ 请记住，我的数据集通常是6500-15000行，大约150-250列; 我最终在一个自动化的VBA批处理脚本中传输了大约20-150个文件。
我已经尝试了几种从Excel表格（VBA）向SQL 2008获取大量数据的方法。我已经列出了以下内容：
方法1.将表传递到VBA数组并发送到存储过程（ADO） - 发送到SQL是SLOW
方法2.创建断开的RecordSet加载它，然后同步。 - 发送到SQL非常慢
方法3.将表放入VBA数组中，循环数组并循环（使用分隔符），然后发送到存储过程。 - 发送到SQL SLOW，但比方法1或2快。
方法4.将表放入VBA数组中，循环数组并循环（使用分隔符），然后使用ADO记录集.addnew命令放置每一行。 - 发送到SQL非常快（比方法1-3快大约20倍），但是现在我需要使用单独的过程来分割数据，这会增加很多等待时间。
方法5.将表放入VBA数组中，序列化为XML，作为VARCHAR发送到存储过程并在存储过程中指定XML。 - 发送到SQL INCREDIBLY SLOW（比方法1或2慢100倍）
我错过了什么？
没有一种最快的方法，因为它取决于许多因素。 确保SQL中的索引已配置和优化。 由于每个插入都需要更新索引，所以很多索引都会导致插入/更新性能不佳。 确保您只连接到数据库，并且在操作期间不要打开/关闭它。 当服务器负载最小时运行更新。 您唯一没有尝试过的其他方法是使用ADO Command对象，并发出直接的INSERT语句。 当使用记录集对象的'AddNew'方法时，请确保在插入结束时只发出一个'UpdateBatch'命令。 除此之外，VBA只能运行接受输入的SQL服务器。
编辑：似乎你已经尝试了一切。 在SQL Server中还有一种称为“Bulk-Logged”恢复模式，可以减少写入事务日志的开销。 可能是值得研究的东西。 这可能会很麻烦，因为它需要稍微摆弄数据库恢复模型，但它可能对您有用。
以下代码将在几秒钟（2-3秒）内传输数千个数据。
Dim sheet As Worksheet
Set sheet = ThisWorkbook.Sheets("DataSheet")
Dim Con As Object
Dim cmd As Object
Dim ServerName As String
Dim level As Long
Dim arr As Variant
Dim row As Long
Dim rowCount As Long
Set Con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
ServerName = "192.164.1.11"
'Creating a connection
Con.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=" & ServerName & ";" & _
"Initial Catalog=Adventure;" & _
"UID=sa; PWD=123;"
'Setting provider Name
Con.Provider = "Microsoft.JET.OLEDB.12.0"
'Opening connection
Con.Open
cmd.CommandType = 1 ' adCmdText
Dim Rst As Object
Set Rst = CreateObject("ADODB.Recordset")
Table = "EmployeeDetails" 'This should be same as the database table name.
With Rst
Set .ActiveConnection = Con
.Source = "SELECT * FROM " & Table
.CursorLocation = 3 ' adUseClient
.LockType = 4 ' adLockBatchOptimistic
.CursorType = 0 ' adOpenForwardOnly
.Open
Dim tableFields(200) As Integer
Dim rangeFields(200) As Integer
Dim exportFieldsCount As Integer
exportFieldsCount = 0
Dim col As Integer
Dim index As Integer
index = 1
For col = 1 To .Fields.Count
exportFieldsCount = exportFieldsCount + 1
tableFields(exportFieldsCount) = col
rangeFields(exportFieldsCount) = index
index = index + 1
Next
If exportFieldsCount = 0 Then
ExportRangeToSQL = 1
GoTo ConnectionEnd
End If
endRow = ThisWorkbook.Sheets("DataSheet").Range("A65536").End(xlUp).row 'LastRow with the data.
arr = ThisWorkbook.Sheets("DataSheet").Range("A1:CE" & endRow).Value 'This range selection column count should be same as database table column count.
rowCount = UBound(arr, 1)
Dim val As Variant
For row = 1 To rowCount
.AddNew
For col = 1 To exportFieldsCount
val = arr(row, rangeFields(col))
.Fields(tableFields(col - 1)) = val
Next
Next
.UpdateBatch
End With
flag = True
'Closing RecordSet.
If Rst.State = 1 Then
Rst.Close
End If
'Closing Connection Object.
If Con.State = 1 Then
Con.Close
End If
'Setting empty for the RecordSet & Connection Objects
Set Rst = Nothing
Set Con = Nothing
End Sub
到目前为止，最快的方法是通过T-SQL的BULK INSERT 。
有几个警告。
BULK INSERT命令并指定文件名时，请记住文件名将在运行SQL Server的计算机上解析）。 FIELDTERMINATOR和ROWTERMINATOR值以匹配您的CSV。 对于我来说，最初需要做一些试验和错误的设置，但与我尝试的其他技术相比，性能提升非常显着。
链接地址: http://www.djcxy.com/p/59571.html