vba 插入 INTO NOT EXISTS SQL 访问
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28282127/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert INTO NOT EXISTS SQL access
提问by Neil Caffrey
I am trying to insert records from another table into a table in Access using sql. I have pasted the statement below. I want to insert the records that exist in ImportMetricsIDs01262015 but not in ShouldImportMetricsIDs. It runs perfectly without any errors but it won't insert anything even when I physically add a new record.
我正在尝试使用 sql 将另一个表中的记录插入到 Access 中的一个表中。我已经粘贴了下面的声明。我想插入存在于 ImportMetricsIDs01262015 但不在 ShouldImportMetricsIDs 中的记录。它运行完美，没有任何错误，但即使我物理添加新记录，它也不会插入任何内容。
INSERT INTO ShouldImportMetricsIDsTable ( [Formulary ID], [Market Segment] )
SELECT ImportMetricsIDs01262015.[Formulary ID], ImportMetricsIDs01262015.[Market Segment]
FROM ImportMetricsIDs01262015
WHERE NOT EXISTS (SELECT *
FROM ShouldImportMetricsIDsTable);
回答by Gordon Linoff
You need a correlation clause. The subquery just checks whether or not the table is empty. Something like:
你需要一个相关条款。子查询只检查表是否为空。就像是：
INSERT INTO ShouldImportMetricsIDsTable( [Formulary ID], [Market Segment] )
SELECT im.[Formulary ID], im.[Market Segment]
FROM ImportMetricsIDs01262015 as im
WHERE NOT EXISTS (SELECT 1
FROM ShouldImportMetricsIDsTable as sim
WHERE im.[Formulary ID] = sim.[Formulary ID] AND
im.[Market Segment] = sim.[Market Segment]
);
回答by SoulTrain
You need to correlate your NOT Existquery with the ImportMetricsIDs01262015table
您需要将您的NOT Exist查询与ImportMetricsIDs01262015表相关联
This code is on the assumption that FormularyID is the key in both the tables.
此代码假设 FormularyID 是两个表中的键。
INSERT INTO ShouldImportMetricsIDsTable (
[Formulary ID]
,[Market Segment]
)
SELECT ImportMetricsIDs01262015.[Formulary ID]
,ImportMetricsIDs01262015.[Market Segment]
FROM ImportMetricsIDs01262015
WHERE NOT EXISTS (
SELECT *
FROM ShouldImportMetricsIDsTable
where ShouldImportMetricsIDsTable.[Formulary ID] = ImportMetricsIDs01262015.[Formulary ID]
);
回答by ShamBhagwat
The keyword "TOP" is necessary in access which is made bold and italic in the code
关键字“TOP”在访问中是必需的，在代码中用粗体和斜体表示
INSERT INTO ShouldImportMetricsIDsTable( [Formulary ID], [Market Segment] )
SELECT im.[Formulary ID], im.[Market Segment]
FROM ImportMetricsIDs01262015 as im
WHERE NOT EXISTS (SELECT
***TOP*** 1
FROM ShouldImportMetricsIDsTable as sim
WHERE im.[Formulary ID] = sim.[Formulary ID] AND
im.[Market Segment] = sim.[Market Segment]
);
