Check if a record already exists in SQL Server table before inserting

user_13722336 0
Viewed 936

I have an Excel table with 5 columns and 3 rows with data. Every time the data changes it is uploaded to DB. Below is my code to send data from the Excel table to the existing table in SQL Server. The challenge is, that the database accepts unique ID values. I would be grateful for your help on how to change the code that when ID in an Excel table already exists in the database? The code terminates with the message:

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
sql-insert vba excel sql
2 Answers

I am not exactly certain that the syntax is correct, but you can try something like this:

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

then you can use this to check if ID already exists in your table.

user_45415660

Consider MERGE with ADO parameterization which is supported with ADO Command and avoid concatenation of SQL in VBA strings.

SQL (save below in .sql file; notice use of ? qmarks)

MERGE dbo.TP AS target  
USING (VALUES (?, ?, ?, ?, ?)) 
      AS source(id, date1, date2, reference, price)
  
ON (target.id = source.id)  
WHEN MATCHED THEN
     UPDATE SET target.date1 = source.date1,
                target.date2 = source.date2,
                target.reference = source.reference,
                target.price = source.price  
WHEN NOT MATCHED THEN  
     INSERT (target.ID, 
             target.Date1,
             target.Date2,
             target.Reference,
             target.Price)  
     VALUES (source.ID,
             source.Date1,
             source.Date2, 
             source.Reference,
             source.Price);

VBA (reads in SQL and binds parameters)

Noe: Below is untested code that may need adjustments. Adjust parameter values to align to corresponding ADO DataTypeNum.

Sub IMPORT()
    Dim con As ADODB.Connection, cmd As ADODB.Command
    Dim r As Range

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

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

    cmd.ActiveConnection = con

    ' READ SQL QUERY FROM FILE INTO STRING
    With CreateObject("Scripting.FileSystemObject")
        strSQL = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
    End With

    For Each r In Range("a2", Range("a2").End(xlDown))
        If r.Offset(0, 4).Value <> "" Then
            With cmd
                .CommandText = cmd.CommandText & strSQL
                
                ' BIND PARAMETERS WITH ? IN SQL (ALIGN VALUES TO ad TYPES)
                .Parameters.Append .CreateParameter("idparam", adVarChar, adParamInput, , r.Offset(0, 0).Value)
                .Parameters.Append .CreateParameter("dt1param", adDate, adParamInput, , r.Offset(0, 1).Value)
                .Parameters.Append .CreateParameter("dt2param", adDate, adParamInput, , r.Offset(0, 2).Value)
                .Parameters.Append .CreateParameter("refparam", adVarChar, adParamInput, , r.Offset(0, 3).Value)
                .Parameters.Append .CreateParameter("priceparam", adDecimal, adParamInput, , r.Offset(0, 4).Value)

                .Execute
            End With
        End If
    Next r

    cmd.close: con.Close
    Set cmd = Nothing: Set con = Nothing

    Msgbox "Import successful"
End Sub
user_14224510
Related