[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1202
  • Last Modified:

SQL VBScript check database first before appending new record

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
10/13/2007,4239,4195,8075,36.13,0.00,74.46,2067,2046,10247,21.55,0.00,73.99,0

script
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
Next

If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"

Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

oDB.Close
0
mweidner
Asked:
mweidner
  • 2
1 Solution
 
dqmqCommented:
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"

0
 
mweidnerAuthor Commented:
Primary key idea and code both work, I'll stick with the PK....thanks!
0
 
dqmqCommented:
Good choice.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now