使用VBA仅向SQL表中插入新记录

Question

我有一个带以下代码的Excel工作簿 -

Sub Button1_Click()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sFirstName, sLastName As String

    With Sheets("Sheet1")

        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;" & _
            "Data Source=server1;" & _
            "Initial Catalog=table1;" & _
            "User ID=user1; Password=pass1"

        'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sFirstName = .Cells(iRowNo, 1)
            sLastName = .Cells(iRowNo, 2)

            'Generate and execute sql statement
            ' to import the excel rows to SQL Server table
            conn.Execute "Insert into dbo.Customers (FirstName, LastName) " & _
                         "values ('" & sFirstName & "', '" & sLastName & "')"

            iRowNo = iRowNo + 1
        Loop

        MsgBox "Customers imported."

        conn.Close
        Set conn = Nothing

    End With

End Sub

这会打开到我的数据库的连接，并输入指定列中的值。

主键是数据库上的一个递增键。问题是它将复制所有值。

我想将新的数据行添加到Excel表格中，只插入那些不存在的行。

我尝试了不同的方法（'merge'、'if exist'、'if not exist' 等），但是我做不对。

解决方案必须通过 VBA。使用 SSMS 设置链接不是一个选择。

我知道可能可以使用临时表，然后触发执行合并操作的过程，但我希望将其作为最后的选择。还没有研究过（正在阅读我的 MS SQL 圣经书），但我希望不需要这样做。

---@Kannan 的答案更新---

VBA 的新部分 -

conn.Execute "IF EXISTS (SELECT 1 FROM dbo.Customers WHERE FirstName = '" &      sFirstName & "' and LastName = '" & sLastName & "') " & _
             "THEN UPDATE dbo.customers SET WHERE Firstname = '" & sFirstName & "' and LastName = '" & sLastName & "' " & _
             "ELSE INSERT INTO dbo.Customers (FirstName, LastName) " & _
             "VALUES ('" & sFirstName & "', '" & sLastName & "')"

这会返回错误“Incorrect syntax near the keyword 'THEN'。”。

- Jamsandwich

Answer 1

你的 SQL 查询语句不太正确 - 在 SQL 的 IF 语句中不存在 THEN 关键字。

而且，如果它（指查询结果）已经存在，你不需要做任何操作，所以只需使用 if not exists。

"IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE FirstName = '" & sFirstName & "' and LastName = '" & sLastName & "') " & _
         "INSERT INTO dbo.Customers (FirstName, LastName) " & _
         "VALUES ('" & sFirstName & "', '" & sLastName & "')"

- BeanFrog

---

非常准确。谢谢BeanFrog。我之前尝试过IF NOT EXISTS，但显然没有做对。我当然没有使用“Select 1”这部分。你能告诉我SELECT 1是如何工作的吗？ - Jamsandwich

这只是一个普通的select语句，但是你选择数字1而不是任何列。这样做的唯一作用就是防止获取所有列详细信息时产生的开销，当你只需要知道“这一行是否存在”而不是“这一行中有什么”的时候。 - BeanFrog

太棒了。再次感谢。一旦我开始专门从事SQL工作，这将为我提供很好的帮助。 - Jamsandwich

Answer 2

这对你来说应该足够了。

Sub Button_Click()
'TRUSTED CONNECTION
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String

    Dim server, username, password, table, database As String


    With Sheets("Sheet1")

            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text


            If con.State <> 1 Then

                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                'con.Open

            End If
            'this is the TRUSTED connection string

            Set rs.ActiveConnection = con

            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If

            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10

            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)

                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

                intImportRow = intImportRow + 1
            Loop

            MsgBox "Done importing", vbInformation

            con.Close
            Set con = Nothing

    End With

Exit Sub

errH:
    MsgBox Err.Description
End Sub

添加一个引用： Microsoft ActiveX 数据对象 2.8 库

顺带一提，我的表格看起来像这样。

- user7075507

Answer 3

您可以使用类似以下的 SQL 查询语句：

IF Exists ( Select 1 from dbo.customers where Firstname = '" & sFirstName & "' and LastName = '" & sLastName & "' THEN Update dbo.customers set --other columns where Firstname = '" & sFirstName & "' and LastName = '" & sLastName & "'
ELSE Insert into dbo.Customers (FirstName, LastName) " & _
                     "values ('" & sFirstName & "', '" & sLastName & "')"

不确定 Excel 和 C# 的语法，您可以类似于此查询进行更正

- Kannan Kandasamy

---

嗨@Kannan，我已将此添加到我的代码并更新了我的问题。您知道我可能会遇到我提到的错误的原因吗？看起来您在第一行中错过了一个闭括号。我认为我已经在正确的位置添加了它。 - Jamsandwich

另外，我不太确定如何输入set命令。我假设“--其他列”是注释部分？所以我把它删掉了。 - Jamsandwich