是否有更快的方法将Excel中的行导出到SQL Server？

Question

我有8,500行36列需要插入到SQL Server表中，目前需要9分钟。我想知道是否有更快的方法来做到这一点。我已经阅读了有关批量插入的内容，但我将其保存为最后的手段。

目前我的做法是，我使用了for each循环，并使用if语句检查每个单元格的格式是否正确(这可能是它很慢的原因，但我需要它)。我正在将它们连接起来，以便一次导出。

提前谢谢。

Answer 1

首先，您需要在SQl-server实例上安装此驱动程序(Microsoft.ACE.OLEDB.12.0

Download Here - Driver

然后在SQL-Sever上执行以下查询：

  select * 
  into mytable
  from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=c\desktop\myExcelfile.xlsm')...[mySheet$] 

不要删除此字符"$“

Answer 2

通过使用数组和使用缓冲区构建查询，可以极大地提高性能。构建一个包含10000行的查询应该只需要几毫秒。下面是一个示例：

Sub build_query()
  Dim buffer As String, length&, data(), r&, c&

  ' load the data in an array '
  data = Range("A2:C5")

  ' create the string buffer '
  buffer = String$(2048, vbNullChar)

  ' write the header '
  Append buffer, length, "INSERT INTO [MyTable] ('ColA', 'ColB', 'ColB') Values(" & vbCrLf

  ' iterate each cell '
  For r = 1 To UBound(data)
    Append buffer, length, "("
    For c = 1 To UBound(data, 2)
      If c > 1 Then Append buffer, length, ","

      ' convert each type '
      Select Case VarType(data(r, c))
        Case vbDate:
          Append buffer, length, format(data(r, c), "'yyyy-mm-dd HH:nn:ss'")
        Case vbString:
          Append buffer, length, "'" & data(r, c) & "'"
        Case Else:
          Append buffer, length, CStr(data(r, c))
      End Select

    Next
    Append buffer, length, ")," & vbCrLf
  Next

  ' write the footer '
  length = length - 3 ' remove comma '
  Append buffer, length, ");"

  ' display the result '
  Debug.Print Left$(buffer, length)
End Sub

Private Sub Append(buffer$, length&, text$)
  length = length + Len(text)
  If length > Len(buffer) Then
    buffer = buffer & String$(Len(buffer) * (length \ Len(buffer)), vbNullChar)
  End If
  Mid$(buffer, length - Len(text) + 1) = text
End Sub

Answer 3

我已经创建了一个Excel外接程序，您可以在其中将Excel数据导出到SQL Server。执行了一个包含8500行和40列的测试，将Excel中的行插入到SQL Server花费了4.5秒。

我把它作为商业产品出售，但如果这是一个有时间限制的工作，你可以免费使用全功能试用版(如果需要，只需给我发一封电子邮件，我会为你延长试用)。

你可以在这里下载http://sqlspreads.com/download-sql-spreads/