Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Insert INTO NOT EXISTS SQL access

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.

INSERT INTO ShouldImportMetricsIDsTable ( [Formulary ID], [Market Segment] )
SELECT ImportMetricsIDs01262015.[Formulary ID], ImportMetricsIDs01262015.[Market Segment]
FROM ImportMetricsIDs01262015
WHERE NOT EXISTS (SELECT *
FROM ShouldImportMetricsIDsTable);
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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]
                     );

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...