SQL VBScript check database first before appending new record

Posted on 2007-10-14
Last Modified: 2008-01-09
I need the SQL in this script to check before appending the database, if exist a record with the same date in field F1 (which is the date field) AND is = to F2 to quit instead of adding another record with the same data.  

SQL would think like this - Hey, this guy has a habit of clicking on this script several times a day and he gets duplicate entries, so lets check the database against the .csv file first, we may not have to do anything.  Along with F1 the date we will check F2 because F2 is never the same.  If we find a identical record we will terminate without even informing him.  If all is ok we will append the database with a new record.

csv file

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Const DB_NAME = "C:\rubyfiles\tanks\gasdata.mdb"
Const TBL_NAME = "Reg_VR_data"
Const DATA_SOURCE = "[Text;HDR=NO;FMT=Delimited;Database=C:\rubyfiles\tanks\;].regular_data.csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If

If blTExists Then

& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

Question by:mweidner
    LVL 42

    Accepted Solution

    Have you considered adding a primary key to the table?  A primary key will prevent the duplicates from being added.

    If a PK does not suit your purpose, try this:

    If blTExists Then
    strSQL = "INSERT INTO " & TBL_NAME _
    & " FROM (SELECT F1, F2, Col3, ..., Col14 ...  FROM " & DATA_SOURCE & ") tmp" _
    & " LEFT JOIN " & TBL_NAME AS T & " on T.F1 = tmp.F1 and T.F2=tmp.F2" _
    & " WHERE T.F2 IS NULL"


    Author Comment

    Primary key idea and code both work, I'll stick with the PK....thanks!
    LVL 42

    Expert Comment

    Good choice.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now