ms access插入无重复项 [英] ms access insert into no duplicates

问题描述

我需要插入访问数据库。但是，如果表中已经有列dup，则跳过该行。

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>

如果我不将dup作为主键。然后它将插入重复项。如果我将dup作为主键。然后我得到一个错误。

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.

我认为我需要类似MySQL的东西。

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

或者像在Oracle中那样处理dup_val_on_index异常。

Or maybe dup_val_on_index exception handling like in Oracle.

@Gord Thompson

@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>

但是我得到[Microsoft] [ODBC Microsoft Access Driver] COUNT字段不正确。

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

是什么？在您的选择语句中？

What are the ? in your select statement?

推荐答案

更新：

如果由于某些原因，您无法直接查询外部数据库，请尝试对 Gord Thompson的建议使用变体。它的效率不如INSERT / SELECT .. FROM Table，但是...将与MS Access一起使用。

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)">
       )

---

小时查询中的数据源是什么？ MS Access支持查询某些外部数据库 （MS Access，SQL Server，Excel等）直接使用IN子句。示例：

---

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'

因此，如果数据来自（受支持的）外部数据库或同一数据库中的另一个表，跳过 cfloop 并运行单 INSERT INTO / SELECT查询的效率更高。只需将其与OUTER JOIN或NOT EXISTS子句结合使用即可排除目标表中已经存在的记录。

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.

免责声明-我已经很长时间没有使用Access了，但是语法应该是这样的：

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

选项＃1-外部联接

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

选项2-不存在

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
       )

这篇关于ms access插入无重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！