Insert INTO NOT EXISTS SQL access

Question

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

score 3 · Accepted Answer · edited Oct 24 '19 at 17:56

3

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

edited Oct 24 '19 at 17:56

June7

19,874
8
24
34

answered Feb 02 '15 at 16:26

Gordon Linoff

1,242,037
58
646
786

Where is `i` defined? – Neil Caffrey Feb 02 '15 at 16:33

score 0 · Answer 2 · answered Feb 02 '15 at 16:27

You need to correlate your NOT Exist query with the ImportMetricsIDs01262015 table

This code is on the assumption that FormularyID is the key in both the tables.

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

score 0 · Answer 3 · answered Apr 18 '17 at 08:36

The keyword "TOP" is necessary in access which is made bold and italic in the code

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

Tested and works without TOP. If use TOP then need a field name or *: `SELECT TOP 1 *`. — June7, Oct 24 '19 at 18:01

Insert INTO NOT EXISTS SQL access

3 Answers3

Linked