sql-server - 使用 VBA 从 Excel 到 SQL Server 表的 INSERT INTO 语句-6ren

sql-server - 使用 VBA 从 Excel 到 SQL Server 表的 INSERT INTO 语句

转载作者：行者123 更新时间：2023-12-04 21:30:41

下面的代码旨在简单地将 Excel 工作表中的信息行添加到 SQL Server 中的表中。它写得很乏味，它逐行和逐个单元地进行。它运行时没有错误弹出窗口，但是当我检查表中的数据时，那里什么也没有。

我一直在 SQL 中查询以使用 WHERE 进行检查子句和“分析师”字段应与我的姓名匹配。什么都没有弹出。

为什么我的数据没有显示在表格中？和我愿意听取任何人关于更好方法的建议。谢谢!

Public Sub ConnectToDB()
    Dim DBCONT As Object
    Dim strConn As String
    Dim Server_Name As String
    Dim Database_Name As String
    Dim Table_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim strSQL As String
    Dim rs As Object
    Dim Fields As String
    Dim LastRowAudit As Long
    Dim i As Long

    Dim sAuditType, sClaimeReceivedDate, sDateAssigned, sDateCompleted, sAnalyst, sCustomer, sID, sAffiliate, sFacility, sDEA, sAcctNumber, sWholesaler, sVendor, sProduct, sNDC, sRef, sClaimedContract, sClaimedContractCost, sContractPriceStartDate, sContractPriceEndDate, sCatalogNumber, sInvoiceNumber, sInvoiceDate, sChargebackID, sContractIndicator, sUnitCost, sWAC, sPotentialCreditDue, sQTY, sSpend, sIpDshIndicator, sDSHorHRSANumber, sUniqueGPOCode, sComment, sResCode, sCorrectCost, sCRRBCM, sCRRBRebill, sCRRBDate As String

'   SET ALL VARIABLES
    Server_Name = "I have this in my actual code" ' Enter your server name here
    Database_Name = "I have this in my actual code" ' Enter your database name here
    Table_Name = "I have this in my actual code"
    User_ID = "I have this in my actual code" ' enter your user ID here
    Password = "I have this in my actual code" ' Enter your password here


    WkbName = ThisWorkbook.Name
    SheetName = "Audit Data" '  WHERE RS IS


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'   SET SQL STRING

    strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
    " ([Audit],[Audit Type],[Claim Received Date],[Date Assigned],[Date Completed]," & _
    " [Analyst],[Customer],[ID],[Affiliate],[Facility],[DEA],[Acct Number],[Wholesaler],[Vendor],[Product],[NDC],[Ref],[Claimed Contract]," & _
    " [Claimed Contract Cost],[Contract Price Start Date],[Contract Price End Date],[Catalog Number],[Invoice Number],[Invoice Date]," & _
    " [Chargeback ID],[Contract Indicator],[Unit Cost],[WAC],[Potential Credit Due],[Qty],[Spend],[IP-DSH indicator Y/N]," & _
    " [DSH and/or HRSA Number],[Unique GPO Code],[Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
    " VALUES ('" & sAudit & "', '" & sAuditType & "', '" & sClaimeReceivedDate & "', '" & sDateAssigned & "', '" & sDateCompleted & "', '" & sAnalyst & "', '" & sCustomer & "', '" & sID & "', '" & sAffiliate & "', '" & sFacility & "', '" & sDEA & "', '" & sAcctNumber & "', '" & sWholesaler & "', '" & sVendor & "', '" & sProduct & "', '" & sNDC & "', '" & sRef & "', '" & sClaimedContract & "', '" & sClaimedContractCost & "', '" & sContractPriceStartDate & "', '" & sContractPriceEndDate & "', '" & sCatalogNumber & "', '" & sInvoiceNumber & "', '" & sInvoiceDate & "', '" & sChargebackID & "', '" & sContractIndicator & "', '" & sUnitCost & "', '" & sWAC & "', '" & sPotentialCreditDue & "', '" & sQTY & "', '" & sSpend & "', '" & sIpDshIndicator & "', '" & sDSHorHRSANumber & "', '" & sUniqueGPOCode & "', '" & sComment & "', '" & sResCode & "', '" & sCorrectCost & "', '" & sCRRBCM & "', '" & sCRRBRebill & "', '" & sCRRBDate & "')"


    Debug.Print strSQL

'   SET TO CONNECTION VARIABLES
    Set DBCONT = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

'   LOOP THROUGH AND APPEND TO TABLE
    ThisWorkbook.Worksheets("Audit Data").Select
    LastRowAudit = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    With ActiveSheet

    DBCONT.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"



        For i = 2 To LastRowAudit
            sAudit = Cells(i, 1)
            sAuditType = Cells(i, 2)
            sClaimeReceivedDate = Cells(i, 3)
            sDateAssigned = Cells(i, 4)
            sDateCompleted = Cells(i, 5)
            sAnalyst = Cells(i, 6)
            sCustomer = Cells(i, 7)
            sID = Cells(i, 8)
            sAffiliate = Cells(i, 9)
            sFacility = Cells(i, 10)
            sDEA = Cells(i, 11)
            sAcctNumber = Cells(i, 12)
            sWholesaler = Cells(i, 13)
            sVendor = Cells(i, 14)
            sProduct = Cells(i, 15)
            sNDC = Cells(i, 16)
            sRef = Cells(i, 17)
            sClaimedContract = Cells(i, 18)
            sClaimedContractCost = Cells(i, 19)
            sContractPriceStartDate = Cells(i, 20)
            sContractPriceEndDate = Cells(i, 21)
            sCatalogNumber = Cells(i, 22)
            sInvoiceNumber = Cells(i, 23)
            sInvoiceDate = Cells(i, 24)
            sChargebackID = Cells(i, 25)
            sContractIndicator = Cells(i, 26)
            sUnitCost = Cells(i, 27)
            sWAC = Cells(i, 28)
            sPotentialCreditDue = Cells(i, 29)
            sQTY = Cells(i, 30)
            sSpend = Cells(i, 31)
            sIpDshIndicator = Cells(i, 32)
            sDSHorHRSANumber = Cells(i, 33)
            sUniqueGPOCode = Cells(i, 34)
            sComment = Cells(i, 35)
            sResCode = Cells(i, 36)
            sCorrectCost = Cells(i, 37)
            sCRRBCM = Cells(i, 38)
            sCRRBRebill = Cells(i, 39)
            sCRRBDate = Cells(i, 40)



            DBCONT.Execute strSQL

         Next i
    End With

    Call CloseDB

    MsgBox i & " Lines Imported."

End Sub

Sub CloseDB()
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    DBCONT.Close
    Set DBCONT = Nothing
End Sub

最佳答案

重新考虑将大量 Excel 迁移到 SQL Server 以获得多种解决方案:

SQL 服务器 :将 Excel 数据保存为文本格式(.txt、.tab、.csv)并使用任意数量的 SQL Server 批量处理操作:bcp utility , OPENROWSET , OPENDATASOURCE , import wizard , 和 BULK INSERT .将此过程放在要在数据库内部运行的存储过程中。

下面是两个等价的 OPENROWSET Excel 工作簿上的示例，其中不处理单个值但处理整个表操作:

-- OLEDB PROVIDER
INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...) 
SELECT COl1, Col2, Col3, ... 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Path\To\Excel\Workbook.xlsx; HDR=YES; IMEX=1', [Sheet$]);

-- ODBC DRIVER
INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...) 
SELECT COl1, Col2, Col3, ...
FROM OPENROWSET('MSDASQL',
     'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
      DBQ=C:\Path\To\Excel\Workbook.xlsx', 'SELECT * FROM [Sheet$]');

微软访问 : 使用 Excel 的数据库兄弟 Access 作为源和目标源之间的媒介。具体来说，将 Excel 工作表和 SQL Server 表设置为 linked tables您可以在其中运行简单的附加查询:

INSERT mySQLServerTable (Col1, Col2, Col3, ...) 
SELECT Col1, COl2, Col3 ... 
FROM myExcelTable

微软 Excel : 如果您必须在 Excel 中运行，请使用带有 ADO Command object 的参数化并避免使用大量引号将 VBA 值连接到 SQL 代码的需要。

... same as above...

' PREPARED STATEMENT
strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
         "    ([Audit], [Audit Type], [Claim Received Date], [Date Assigned], [Date Completed]," & _
         "     [Analyst], [Customer], [ID], [Affiliate], [Facility], [DEA], [Acct Number], [Wholesaler]," & _
         "     [Vendor], [Product], [NDC], [Ref], [Claimed Contract], [Claimed Contract Cost]," & _
         "     [Contract Price Start Date], [Contract Price End Date], [Catalog Number], [Invoice Number], [Invoice Date]," & _
         "     [Chargeback ID], [Contract Indicator], [Unit Cost],[WAC], [Potential Credit Due]," & _
         "     [Qty], [Spend],[IP-DSH indicator Y/N], [DSH and/or HRSA Number], [Unique GPO Code]," & _ 
         "     [Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
         " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," _
         "         ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

DBCONT.Open ...

Set sh = ActiveSheet

For i = 2 To LastRowAudit
    Set cmd = CreateObject("ADODB.Command")

    With cmd
      .ActiveConnection = DBCONT   ' CONNECTION OBJECT
      .CommandText = strSQL        ' SQL STRING     
      .CommandType = adCmdText

      ' BINDING PARAMETERS
      .Parameters.Append .CreateParameter("sAuditParam", adVarChar, adParamInput, 255, sh.Cells(i, 1))
      .Parameters.Append .CreateParameter("sAuditTypeParam", adVarChar, adParamInput, 255, sh.Cells(i, 2))
      .Parameters.Append .CreateParameter("sClaimeReceivedDateParam", adVarChar, adParamInput, 255, sh.Cells(i, 3))

       '... rest of parameters

      .Execute                     ' RUN ACTION
    End With

    Set cmd = Nothing
Next i