sql - 在插入之前检查 SQL Server 表中是否已存在记录

Question

我有一个包含 5 列和 3 行数据的 Excel 表。每次数据更改时，它都会上传到数据库。下面是我将数据从 Excel 表发送到 SQL Server 中现有表的代码。挑战在于，数据库接受唯一的 ID 值。如果您在 Excel 表中的 ID 已存在于数据库中时如何更改代码，我将不胜感激？代码以以下消息终止:

Duplicates are not allowed!

Sub IMPORT()

    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command

    Set con = New ADODB.Connection
    Set cmd = New ADODB.Command

    Dim r As Range

    con.ConnectionString = sqlconstr
    con.Open "Provider=sqloledb;Data Source=DS;Initial Catalog=AUTO;Integrated Security=SSPI"

    cmd.ActiveConnection = con

    For Each r In Range("a2", Range("a2").End(xlDown))

        If r.Offset(0, 4).Value <> "" Then

            cmd.CommandText = cmd.CommandText & _
            GetInsertText( _
                r.Offset(0, 0).Value, _
                r.Offset(0, 1).Value, _
                r.Offset(0, 2).Value, _
                r.Offset(0, 3).Value, _
                r.Offset(0, 4).Value _
            )

        End If

    Next r

    Debug.Print cmd.CommandText

    cmd.Execute

    con.Close
    Set con = Nothing

    msgbox "Import successful"

End Sub

Function GetInsertText(ID As String, Date1 As Date, Date2 As Date, Reference As String, Price As Double)

    sql = _
        "insert into dbo.TP (" & _
        "ID, Date1, Date2, Reference, Price)" & _
        "values (" & _
        "'" & ID & "'," & _
        "'" & Format(Date1, "yyyy-mm-dd") & "'," & _
        "'" & Format(Date2, "yyyy-mm-dd") & "'," & _
        "'" & Reference & "'," & _
        Replace(Format(Price, "#0.00"), ",", ".") & ");"

    GetInsertText = sql

End Function 

Answer 1

我不确定语法是否正确，但你可以尝试这样的事情:

Function ExistsAlready(ID As String, conn as Object) As Boolean

  Dim cmd As ADODB.Command

  Set cmd = New ADODB.Command

  cmd.CommandText = "select count(*) from dbo.TP where ID = '" & ID & "'"
  cmd.ActiveConnection = con
  Set rs = cmd.Execute

  ExistsAlready= rs.Fields(0).Value

End Function 

那么您可以使用它来检查您的表中是否已经存在 ID。