INSERT ... WHERE NOT EXISTS ...

[KH]

Hi,

I can't seem to get the syntax right for an insert query. In Sql Server I
would do this:

INSERT INTO [bla] (a, b, c)
SELECT 0, 'foo', NULL
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

So, don't insert the new row if there's already a row where (a = 0).
But Access complains that there's no table in the SELECT query.

I tried a derived table to no avail, and this:

INSERT INTO [bla] (a, b, c)
SELECT TOP 1 0, 'foo', NULL
FROM [bla]
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

but that doesn't work if [bla] has no rows.

Anybody know how to do that in Access? Thanks in advance!

- KH

 

[Piet Linden]

Hi,

I can't seem to get the syntax right for an insert query. In Sql Server I
would do this:

INSERT INTO [bla] (a, b, c)
SELECT 0, 'foo', NULL
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

So, don't insert the new row if there's already a row where (a = 0).
But Access complains that there's no table in the SELECT query.

I tried a derived table to no avail, and this:

INSERT INTO [bla] (a, b, c)
SELECT TOP 1 0, 'foo', NULL
FROM [bla]
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

but that doesn't work if [bla] has no rows.

Anybody know how to do that in Access? Thanks in advance!

- KH

Do you mean that you have two tables/sets which partially overlap
(contain some of the same records) and you just want to add the
missing ones from one set to the other?

If that's the case, the easiest way is to run the "Find unmatched"
query wizard and then turn that into an append query. What it amounts
to is something like this:

SELECT ...
FROM a LEFT JOIN b ON a.joinfield=b.joinfield
WHERE b.joinfield IS NULL

Then you turn that into an append query...

Or did I misunderstand what you were asking (wouldn't be the first
time...)

 

[John Spencer MVP]

Try the alternative syntax

INSERT INTO [Bla], (a, b, c)
VALUES (0,'foo',Null)
WHERE NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KH]

Thank you all for the pointers! John Spencer's suggestion is what I was
looking for, but still gives the error that there's no table in the query.
Btw I'm using Access 07 but with an 02/03 .mdb - don't know if that would
make a diff in this situation, but I should have given that info in the
original post.

Anyways I ended up creating a numbers table (link below) and doing this:

INSERT INTO [bla] (a,b,c)
SELECT 0, 'foo', NULL
FROM numbers
WHERE n = 1 AND NOT EXISTS(SELECT * FROM [bla] WHERE a = 0)

Cheers! - KH

http://sqlserver2000.databases.aspf...onsider-using-an-auxiliary-numbers-table.html