ms access insert into no duplicates

Question

I need to insert into an access database. But skip the row if column dup is already in the table.

What I have so far is this:

<cfoutput  query="hours">
<cfquery name="Employee" datasource="test">

INSERT INTO Tbl_Hours_Comments
(ID, ship_num, dup)
    values(#hours.id#, #hours.ship#, #hours.dup#)
</cfquery>
</cfoutput>

If I don't make dup a Primary Key. Then it will INSERT duplicates. If I make dup a Primary Key. Then I get an error.

I think I need something like MySQL. ON DUPLICATE KEY UPDATE.

Or maybe dup_val_on_index exception handling like in Oracle.

@Gord Thompson

I tried (Not sure if I'm doing it right):

<cfoutput  query="hours">
<cfquery name="Insert_Employee" datasource="trung">
INSERT Tbl_Hours_Comments (ID, ship_num, dup)

values(#hours.id#, #hours.ship#, #hours.dup#)
SELECT ? as dup
  FROM( SELECT COUNT(*) as n from Tbl_Hours_Comments) as Dual



WHERE NOT EXISTS
        (   SELECT *
            FROM Tbl_Hours_Comments
            WHERE dup = ?
        )


</cfquery>
</cfoutput>

But I get [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect .

What are the ? in your select statement?

Answer 1

If you are using the Microsoft Access ODBC driver to update the database then you can use INSERT ... SELECT ... WHERE NOT EXISTS. The following example is in Python, but it looks like you should be able to do something similar in ColdFusion.

import pyodbc
cnxn = pyodbc.connect("DSN=db1")  # Microsoft Access ODBC
crsr = cnxn.cursor()

hours_ship = "ship102"  # test data
hours_dup = 3           #

sql = """\
INSERT INTO Tbl_Hours_Comments (ship_num, dup)
SELECT ? as ship_num, ? AS dup
FROM (SELECT COUNT(*) AS n FROM Tbl_Hours_Comments) AS Dual
WHERE NOT EXISTS (SELECT * FROM Tbl_Hours_Comments WHERE dup = ?)
"""
params = (hours_ship, hours_dup, hours_dup)
crsr.execute(sql, params)
cnxn.commit()
print("{} row(s) inserted".format(crsr.rowcount))
crsr.close()
cnxn.close()

Note that this approach will not work using the Microsoft Access OLEDB provider.

Answer 2

Update:

If for some reason you cannot query the external database directly, try using a variation on Gord Thompson's suggestion. It is not as efficient as INSERT /SELECT .. FROM Table, but ... will work with MS Access.

INSERT INTO Tbl_Hours_Comments (ID, ship_num, dup)
SELECT <cfqueryparam value="#hours.id#" cfsqltype="(your column type here)">
       , <cfqueryparam value="#hours.ship#" cfsqltype="(your column type here)">
       , <cfqueryparam value="#hours.dup#" cfsqltype="(your column type here)">
WHERE  NOT EXISTS
       (
          SELECT NULL
          FROM   Tbl_Hours_Comments dupe
          WHERE  dupe.id = <cfqueryparam value="#hours.id#" cfsqltype="(your column type here)">
          AND    dupe.ship = <cfqueryparam value="#hours.ship#" cfsqltype="(your column type here)">
          AND    dupe.dup = <cfqueryparam value="#hours.dup#" cfsqltype="(your column type here)">
       )

---

What is the source of the data in the "hours" query? MS Access supports querying certain external databases (MS Access, SQL Server, Excel, ecetera) directly using an IN clause. Example:

 SELECT Column1, Column2
 FROM   TableName IN 'c:\path\to\external.mdb'

So if the data is from a (supported) external database OR another table within the same database, it is a LOT more efficient to skip the cfloop and run a single INSERT INTO / SELECT query. Just combine it with an OUTER JOIN or NOT EXISTS clause to exclude records that are already exist in the target table.

Disclaimer - I have not used Access in a long while, but the syntax should be something like this:

Option #1 - OUTER JOIN

INSERT INTO Tbl_Hours_Comments (ID, ship_num, dup)
SELECT ex.id, ex.ship, ex.dup
FROM   OtherTable ex LEFT JOIN Tbl_Hours_Comments dupe
          ON dupe.id = ex.id
          AND dupe.ship = ex.ship
          AND dupe.dup = ex.dup
WHERE  dupe.id IS NULL

Option #2 - NOT EXISTS

INSERT INTO Tbl_Hours_Comments (ID, ship_num, dup)
SELECT ex.id, ex.ship, ex.dup
FROM   OtherTable ex
WHERE  NOT EXISTS
       (
          SELECT NULL
          FROM   Tbl_Hours_Comments dupe
          WHERE  dupe.id = ex.id
          AND    dupe.ship = ex.ship
          AND    dupe.dup = ex.dup
       )

Answer 3

I wanted to post what I ended up doing in case it helps someone.

Thank you to @Leigh and @Gord for help on this problem.

This what I finally did to solve it. It is very similar to Leigh and Gord's way of a sub query.

I CFQUERY both tables (QueryA and QueryB).

Then I used this (from https://coldfusionblog.wordpress.com/2012/01/11/how-to-do-an-outer-join-in-query-of-queries/):

<cfquery name="unique">
SELECT *
FROM QueryA
WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#)
</cfquery>

To get a list of element in QueryA that are not in QueryB.

Then I used another insert query to insert all the new unique elements into QueryB.