将excel 中表批量导入到 sqlserver 如何使用insert into

xwfsales

以下为excel 批量导入到   access ,请问如何在VBA excel平台上，将excel 表批量导入到 sqlserver2000,使用insert
Private Sub ADO_UPDATE_ACCESS_Click()
    Dim endline%, line%, addr$, sql$
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & _
                "\通讯录.mdb;Jet OLEDB:Database Password=123"
    endline = [a65536].End(xlUp).Row
    For line = 4 To endline
        conn.Execute ("delete from 档案1 where 姓名='" & Cells(line, "a") & "'") '先整条删除再整条插入比逐字段更新速度应快
    Next
    addr = Range(Cells(3, "a"), Cells(endline, [iv3].End(xlToLeft).Column)).Address(0, 0) '得到数据区的地址
    sql = "INSERT INTO [档案1] SELECT * FROM [Excel 8.0;Database=" & ThisWorkbook.FullName & ";HDR=YES].[sheet1$" & addr & "];"
    conn.Execute sql
    MsgBox "更新完毕"
    conn.Close
    Set conn = Nothing
End Sub
跪求：

thtfzhaobo

同时做两个链接，处理也许更容易些

xwfsales

如何用，请赐一个完事的模块   在excel 工作环境下，批量导excel 到 sqlserver2000  使用insert
我的邮箱: tj103@tom.com,       70167460 (QQ)

thtfzhaobo

提示你一下,你的代码有点小问题
1\addr = Range(Cells(3, "a"), Cells(endline, [iv3].End(xlToLeft).Column)).Address(0, 0) '得到数据区的地址
要用行列数据标识,不能用"A"
2\ sql = "INSERT INTO [档案1] SELECT * FROM [Excel 8.0;Database=" & ThisWorkbook.FullName & ";HDR=YES].[sheet1$" & addr & "];"
addr所选区域必须包含与数据框相同的表头
没有附件,无法帮你修改

jygzcj

已经找到方法了，但是速度不是太理想。
Sub UpdateTable()
   
    On Error GoTo Errhandle
   
    Dim objfl As Variant
   
    Dim rngName As Range
   
    Dim LastRow As Long
   
   
    With Sheet1
        If .FilterMode Then
            .ShowAllData
        End If
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        
        Set rngName = .Range(.Cells(1, 1), .Cells(LastRow, 16))
    End With
   
    rngName.Name = "TempRange"
   
    Dim strFileName As String
   
   
    strFileName = ThisWorkbook.FullName
   
    Dim cnn As Object
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
   
    Dim nSQL As String
    Dim nJoin As String
   
    nSQL = "INSERT INTO [odbc;Driver={SQL Server};UID=consign_user;DataBase=consign;PWD=#202308;Server=10.125.4.33].[CheryReconcileRecord]"
    nJoin = " SELECT * from [TempRange]"
   
    cnn.begintrans
   
    cnn.Execute nSQL & nJoin
   
    cnn.Committrans
   
    MsgBox "数据已经批量更新成功", vbInformation
   
    Exit Sub
   
   
Errhandle:

    cnn.rollbacktrans
    MsgBox "数据更新出错,未更新成功" & Err.Description, vbInformation
   
   
   
        
End Sub

opiona

ADO将百万行Excel数据快速上传到MSSQL或MySQL数据库中 仅需几十秒

https://club.excelhome.net/thread-1666858-1-1.html