追加到另一个表中，避免重复 [英] Appending into another table and avoiding duplicates

问题描述

我正在尝试查询一个表并将结果附加到另一个表中.我尝试创建的查询在表中需要两个字段:

I am attempting to query a table and append the results into another table. The query that I am attempting to create, requires two fields in a table:

[TblCaseListing]
CaseNumber (text field describing an internal case number)
NextRenewalDate (date/time field describing the next due date for renewing the case)

本质上，此表列出了所有可能的案件，以及案件编号的最后更新日期.我正在创建的过程如下:

Essentially this table is a listing of all the possible cases, and the dates that the case number can last be renewed by. The process that I am creating looks like:

1. 查询案例并将其追加到NextRenewalDate(目标[tblCaseNumberQueue])之前90天
2. 案例由用户处理
3. 如果项已完成，则TblCaseListing.NextRenewalDate将更新为下一年

现在我的查询如下:

Field: CaseNumber
Criteria: Not In (SELECT DISTINCT CaseNumber FROM tblCaseNumberQueue) 'problem is here

Field: NextRenewalDate
Criteria: <=DateAdd("d",90,Date()) 'detect and append from 90 days

但是，这是不正确的.由于NextRenewalDate将通过用户工作表单并更新数据逐年更改.我想在90天内追加，只要续订日期和案例编号组合在一起在目标表中已经不存在.此查询对我说:如果今天的日期在90天内且案件编号不在目标表中，则追加".它应该说:如果案例编号和下一个续约日期不在目的地表中，并且NextRenewalDate在90天之内，则追加.我需要修改什么才能在两个字段的基础上追加?谢谢！

However, this is not correct. Since the NextRenewalDate will change year after year by Users working a form and updating the data. I want to append within 90 days as long as the renewal date and case number combined dont already exist in the destination table together. This query to me says "Append if today's date is within 90 days and the case number is not in the destination table". It should say "Append if the case number combined with next renewal date are not in the destination table, and the NextRenewalDate is within 90 days. What do I need to modify in order to append based on the two fields? Thanks!

推荐答案

在我看来，您可以通过以下两种方式之一来处理此问题.您感兴趣的案例集很容易定义

It seems to me that you can handle this in one of two ways. The set of cases in which you are interested is easily defined

SELECT CaseNumber, NextRenewalDate
FROM TblCaseListing
WHERE NextRenewalDate <= DateAdd("d", 90, Date())

您的第一个选择是使用NOT EXISTS检查目标表中是否有已插入的案例

Your first option would be to use NOT EXISTS to check the destination table for cases that have already been inserted

INSERT INTO tblCaseNumberQueue (CaseNumber, NextRenewalDate)
SELECT CaseNumber, NextRenewalDate
FROM TblCaseListing
WHERE NextRenewalDate <= DateAdd("d", 90, Date())
    AND NOT EXISTS
        (
            SELECT * FROM tblCaseNumberQueue
            WHERE tblCaseNumberQueue.CaseNumber = TblCaseListing.CaseNumber
                AND tblCaseNumberQueue.NextRenewalDate = TblCaseListing.NextRenewalDate
        )

但是，有些人不赞成使用NOT EXISTS，因为它效率不高.

However, some people frown upon using NOT EXISTS because it is not very efficient.

另一种选择是在[tblCaseNumberQueue]中创建唯一索引

The other option would be to create a unique index in [tblCaseNumberQueue]

CREATE UNIQUE INDEX UX_CaseDate ON tblCaseNumberQueue (CaseNumber, NextRenewalDate)

，然后在不进行NOT EXISTS检查的情况下进行INSERT操作，并让唯一索引禁止已经存在的行.

and then just do the INSERT without the NOT EXISTS check and let the unique index disallow the rows that are already there.

INSERT INTO tblCaseNumberQueue (CaseNumber, NextRenewalDate)
SELECT CaseNumber, NextRenewalDate
FROM TblCaseListing
WHERE NextRenewalDate <= DateAdd("d", 90, Date())

但是，有些人也不赞成这种方法.他们声称，我们应始终避免错误，而不要依靠这种机制进行其他合法"操作.

However, some people frown on that approach, too. They claim that we should always avoid errors and not rely on such mechanisms for otherwise "legitimate" operations.

就个人而言，我可能会选择第一个选项，因为它更加明确，只有在我认为NOT EXISTS可能会显着影响性能的情况下，才选择第二个选项. (但是，无论哪种情况，我都建议您创建UNIQUE INDEX，因为您确实要做要避免重复，无论您选择哪种方式避免重复.)

Personally, I would probably go with the first option because it is more explicit, and only go with the second option if I thought that NOT EXISTS might be significantly impacting performance. (However, I would recommend creating the UNIQUE INDEX in either case since you really do want to avoid duplicates, whichever way you choose to avoid them.)

这篇关于追加到另一个表中，避免重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！