追加如果该项目在另一个表中不存在? [英] Append If the item does not exist in another table?

问题描述

我有一个表记录源(包含大量信息)，并且我的表包含1200条记录.我想将当前不存在的任何记录追加到此目标表(包含1200条记录)中.我要附加的商品的标准是:

I have a table record source (contains tons of info), and I have a table that contains 1200 records. I would like to append into this destination table (containing 1200 records) any records that currently do not exist. the criteria for my items to be appended is:

"Not In ([TABLE - To Work].[Item Number])"

问题是，它返回了我想要的记录，返回了1200次，而不是一次.

Problem is, it is returning the record I want, back 1200 times, instead of once.

例如:

Table A:                    Table B:

Item Number                 Item Number
12345                       45678
45678

"12345"仅会添加到表B中一次(然后再也不会追加！).我寻找了一些解决方案，并尝试使用无与伦比的查询向导，但是我认为这并不是我想要的(它是生成，其中数字为null).我需要做些什么才能对表格的整个范围进行查看，而不是逐项查看(我假设这就是为什么它填充与现有记录相同的次数)的原因?我要走什么步骤?.

"12345" would append into table B only once (and then never append again!) I looked for a few solutions, and I tried using the unmatched query wizard, but I do not think it was really what I Wanted (It generated where the number is null). What do I need to do to make this sort of look at entire scope of the table and not item by item (I assume thats why it is populating the same number of times as existing records)? What step am I leaving out?.

推荐答案

查询的一般形式类似于

INSERT INTO [Table B] ( [Item Number] )
SELECT [Table A].[Item Number]
FROM [Table A]
WHERE [Table A].[Item Number] NOT IN (SELECT [Item Number] FROM [Table B]);

请注意，[表B]不在主查询的FROM子句中，而仅在NOT IN子查询的FROM子句中.

Note that [Table B] is not in the FROM clause of the main query, it is only in the FROM clause of the NOT IN subquery.

这篇关于追加如果该项目在另一个表中不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！