vba 仅将新数据从 Excel 导出到 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2566761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export new data only from Excel to SQL
提问by user307655
The following Excel VBA code exports data from a spreadsheet to SQL:
以下 Excel VBA 代码将数据从电子表格导出到 SQL：
Sub SQLIM()
' Send data to SQL Server
' This code loads data from an Excel Worksheet to an SQL Server Table
' Data should start in column A and should be in the same order as the server table
' Autonumber fields should NOT be included'
' FOR THIS CODE TO WORK
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
Dim Cn As ADODB.Connection
Dim ServerName As String
Dim DatabaseName As String
Dim TableName As String
Dim UserID As String
Dim Password As String
Dim rs As ADODB.Recordset
Dim RowCounter As Long
Dim ColCounter As Integer
Dim NoOfFields As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim shtSheetToWork As Worksheet
Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1")
Set rs = New ADODB.Recordset
ServerName = "WIN764X\sqlexpress" ' Enter your server name here
DatabaseName = "two28it" ' Enter your database name here
TableName = "COS" ' Enter your Table name here
UserID = "" ' Enter your user ID here
' (Leave ID and Password blank if using windows Authentification")
Password = "" ' Enter your password here
NoOfFields = 7 ' Enter number of fields to update (eg. columns in your worksheet)
StartRow = 2 ' Enter row in sheet to start reading records
EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet
' CHANGES
' Dim shtSheetToWork As Worksheet
' Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1")
'********
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";"
rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic
For RowCounter = StartRow To EndRow
rs.AddNew
For ColCounter = 1 To NoOfFields
rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
Next ColCounter
Next RowCounter
rs.UpdateBatch
' Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
I want to modify the code to:
我想将代码修改为：
- Check if data from column A already exists in the SQL Table.
- If the data exists, then only update rather than import as a new role.
- if the data does not exist, then import it as a new role.
- 检查 SQL 表中是否已存在 A 列中的数据。
- 如果数据存在，则只更新而不是作为新角色导入。
- 如果数据不存在，则将其作为新角色导入。
回答by HLGEM
This is the wrong way to do this task. Do this from the SQL server instead. Look up the merge command if you are using SQL Server 2008.
这是执行此任务的错误方法。请改为从 SQL 服务器执行此操作。如果您使用的是 SQL Server 2008，请查找合并命令。
Also consider using SSIS to do this task, it is a better choice than doing it from VBA. OR look up the OPenrowset command in t-SQL and do it that way.
还可以考虑使用 SSIS 来完成这项任务，这是比从 VBA 中完成的更好的选择。或者在 t-SQL 中查找 OPenrowset 命令并这样做。
回答by RingoSchplingo
I would suggest uploading data from Excel to a staging table in SQL and then call (also via VBA) a SQL stored procedure which contains logic appropriate to your needs to merge the data from the staging table into the ultimate destination table.
我建议将数据从 Excel 上传到 SQL 中的暂存表，然后调用（也通过 VBA）一个 SQL 存储过程，其中包含适合您将暂存表中的数据合并到最终目标表的需要的逻辑。
