从Excel(O365)通过VBA快速导入数据至SQL Server的性能优化问询

你当前用ACE OLEDB跨库插入的方式，底层实际是逐行提交数据，再加上Excel作为中间载体的额外开销，才导致10k行耗时15分钟的低效问题。以下是无需服务器权限、纯VBA实现的优化方案：

一、替换驱动为SQL Server专用驱动 #

旧版ODBC驱动性能远不如专用的SQL Server驱动，修改连接字符串可直接提升传输效率：

' 使用ODBC Driver 17 for SQL Server（多数系统默认自带，若缺失可自行安装）
Dim sqlConnStr As String
sqlConnStr = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=sss;DATABASE=salesdb;UID=uuu;PWD=ppp;"

二、ADODB.Recordset批量插入优化（修正参数配置） #

你之前尝试的UpdateBatch效果差，大概率是没配置正确的游标和锁定模式，以下是优化后的代码：

Sub BatchImportViaRecordset()
    Dim ws As Worksheet
    Dim sqlConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    
    ' Excel端基础优化
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Set ws = ThisWorkbook.Worksheets("DATA")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 连接SQL Server
    Set sqlConn = New ADODB.Connection
    sqlConn.Open "DRIVER={ODBC Driver 17 for SQL Server};SERVER=sss;DATABASE=salesdb;UID=uuu;PWD=ppp;"
    
    ' 初始化批量模式Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient ' 客户端游标，支持批量更新
    rs.LockType = adLockBatchOptimistic ' 批量锁定模式
    rs.Open "SELECT * FROM dbo.tbl_sales WHERE 1=2", sqlConn, adOpenStatic ' 仅加载表结构
    
    ' 批量加载数据
    For i = 2 To lastRow ' 跳过表头
        rs.AddNew
        For j = 1 To lastCol
            rs.Fields(j - 1).Value = ws.Cells(i, j).Value
        Next j
        ' 每1000行提交一次，避免内存溢出
        If (i - 1) Mod 1000 = 0 Then
            rs.UpdateBatch
        End If
    Next i
    rs.UpdateBatch ' 提交剩余数据
    
    ' 清理资源
    rs.Close
    sqlConn.Close
    Set rs = Nothing
    Set sqlConn = Nothing
    
    ' 恢复Excel设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

三、分批次生成批量SQL插入 #

将数据按批次拼接成INSERT ... VALUES语句，减少网络交互次数：

Sub BatchImportViaSQL()
    Dim ws As Worksheet
    Dim sqlConn As ADODB.Connection
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, k As Long
    Dim batchSize As Long
    Dim sqlStr As String, valueStr As String
    
    batchSize = 1000 ' 每批次1000行，可根据内存调整
    Set ws = ThisWorkbook.Worksheets("DATA")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 连接SQL Server
    Set sqlConn = New ADODB.Connection
    sqlConn.Open "DRIVER={ODBC Driver 17 for SQL Server};SERVER=sss;DATABASE=salesdb;UID=uuu;PWD=ppp;"
    
    ' 分批次生成SQL
    For i = 2 To lastRow Step batchSize
        sqlStr = "INSERT INTO dbo.tbl_sales VALUES "
        For j = i To WorksheetFunction.Min(i + batchSize - 1, lastRow)
            ' 拼接单行列值，注意转义单引号避免SQL语法错误
            valueStr = "('" & Replace(ws.Cells(j, 1).Value, "'", "''") & "'"
            For k = 2 To lastCol
                valueStr = valueStr & ", '" & Replace(ws.Cells(j, k).Value, "'", "''") & "'"
            Next k
            valueStr = valueStr & ","
            sqlStr = sqlStr & valueStr
        Next j
        sqlStr = Left(sqlStr, Len(sqlStr) - 1) ' 移除最后一个多余逗号
        sqlConn.Execute sqlStr, , adExecuteNoRecords
    Next i
    
    sqlConn.Close
    Set sqlConn = Nothing
End Sub

额外优化建议 #

• 确保目标表tbl_sales无任何约束（主键、外键、默认值等），导入完成后再添加
• 提前对齐Excel数据列与SQL表字段的类型，避免驱动自动转换的额外开销
• 若数据量极大，可将Excel数据临时保存为本地CSV，再通过VBA读取CSV拼接SQL，进一步减少Excel对象操作的开销

内容的提问来源于stack exchange，提问作者Emp24