使用VBA仅将新记录插入SQL表 [英] Insert new records only into SQL Table Using VBA

问题描述

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

I have an Excel workbook with the below code -

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

这将打开与我的数据库的连接，并输入所述列中的值.

This opens up a connection to my database and inputs the values from the stated columns.

主键是数据库上的增量键.问题在于它将复制所有值.

The primary key is an incremental key on the database. The problem is it will copy ALL values.

我想将新的数据行添加到Excel工作表中，仅插入那些尚不存在的行.

I'd like to add new rows of data into the Excel Sheet and only insert those rows that don't already exist.

我尝试了其他方法(合并"，如果存在"，如果不存在等)，但我做对了.

I've tried different methods ('merge', 'if exist', if not exist', etc.) but I can't get it right.

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

The solution has to be through VBA. Setting up a link using SSMS is not an option.

我知道可以使用临时表，然后触发执行合并的过程，但是我想作为最后的手段进行研究.还没有读完它(通过我的MS SQL圣经写书)，但是我希望它没有必要.

I understand that it may be possible to use temporary tables and then trigger a procedure which performs the merge but I want to look into that as a last resort. Haven't read up on it yet (making my way through my MS SQL bible book) but I'm hoping it won't be necessary.

---根据@Kannan的答案进行更新---

---Update from @Kannan's answer---

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 & "')"

这将返回错误关键字'THEN'附近的语法不正确".

This returns error 'Incorrect syntax near the keyword 'THEN'.

推荐答案

您的SQL查询不太正确-SQL IF中没有THEN.

Your SQL query isn't quite right - there is no THEN in a SQL IF.

此外，如果确实存在，您无需执行任何操作，因此，如果不存在，则只需使用即可.

Also, you don't need to do anything if it does exist, so just use 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 & "')"

这篇关于使用VBA仅将新记录插入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！