How to insert records from Excel using VBA to DB2 Database

Question

I want to insert records into a table from data in my workbook, using VBA I do not have a problem with the inserts, but the problem is the duplicates.

How can I have the records from Excel be inserted only if the record does not exist within the database?

This is what I currently have.

Additionally can I have records that do exist be updated or would that require another Module?

Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sequipid, stype, sname As String

    With Sheets("Sheet1")

        'Open a connection to SQL Server
        conn.Open "Driver={IBM DB2 ODBC DRIVER};Database=XXXX;Hostname=192.168.XXX.XX;Port=50000;Protocol=TCPIP;Uid=" & "XXXX" & ";Pwd=" & "XXXXXX" & ";CurrentSchema=LYNX;"

        'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sequipid = .Cells(iRowNo, 1)
            stype = .Cells(iRowNo, 2)
            sname = .Cells(iRowNo, 3)

            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "INSERT INTO OH_TEST_TABLE (EQUIPID, TYPE, NAME) VALUES ('" & sequipid & "','" & stype & "','" & sname & "')"

            iRowNo = iRowNo + 1
        Loop

        MsgBox "RECORD UPDATED"

        conn.Close
        Set conn = Nothing

    End With

End Sub

The easiest (and most robust) way is to add the appropriate unique constraint to the database. It shouldn't be the responsibility of the client code to maintain data integrity in the back-end. — Comintern, Nov 02 '16 at 18:13
First query the database for the current `EQUIPID` - if it exists then run an update, else run an insert. — Tim Williams, Nov 02 '16 at 18:32
@TimWilliams so basically after conn.execute "case when '"&equipid&"' exists (select 1 from oh_test_table where equipid = '"&equipid&"' ) then update oh_test_table etc else insert — OVO, Nov 02 '16 at 18:46
I'm not a DB2 person but this question covers the topic: http://stackoverflow.com/questions/330241/does-db2-have-an-insert-or-update-statement — Tim Williams, Nov 02 '16 at 18:51
@TimWilliams looks like this is for an interaction between two tables within db2, the data values will be coming from the workbook — OVO, Nov 02 '16 at 19:05
You're Googlng for "DB2 upsert" - second hit (just after the one I posted) is http://lpar.ath0.com/2013/08/12/upsert-in-db2/ which covers what you want to do. — Tim Williams, Nov 02 '16 at 19:50
Consider using the `MERGE` statement instead, if it's available in your DB2 version. — mustaccio, Nov 03 '16 at 00:05

score 1 · Answer 1 · answered Nov 03 '16 at 08:13

You could use a merge statement like this to either update or insert (on most modern versions of DB2):

merge into oh_test_table oh1
using (select * from oh_test_table where equipid='"& sequipid &"') oh2
on oh1.equipid = oh2.equipid
when matched then update set
     type = '"& stype &"',
     name = '"& sname &"'
when not matched then insert
     (equipid, type, name) values ('" & sequipid & "','" & stype & "','" & sname & "')

For the sake of clarity I have displayed it across multiple lines, without the extra VBA formatting required.

score 0 · Answer 2 · answered Nov 03 '16 at 06:54

i you are on DB2 iseries you can modify your query like this

 conn.Execute "INSERT INTO OH_TEST_TABLE (EQUIPID, TYPE, NAME) select '" & sequipid & "','" & stype & "','" & sname & "' from SYSIBM.SYSDUMMY1 where not exists (select * from OH_TEST_TABLE where EQUIPID='" & sequipid & "')"

or this

 conn.Execute "INSERT INTO OH_TEST_TABLE (EQUIPID, TYPE, NAME) select * from (values ('" & sequipid & "','" & stype & "','" & sname & "') ) tmp(a,b,c)  where not exists (select * from OH_TEST_TABLE where EQUIPID='" & sequipid & "')"

How to insert records from Excel using VBA to DB2 Database

2 Answers2