exists already in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI, hopefully someone has a simple solution to this:
I'm trying to insert a record if it DOESN'T already exist, in one statement. I'm not using explicit transactions so one statement equates to one transaction.
So I want it to be one statement.. in SQL Server, I would do

if not exists (select * from table1 where mycol = 'myval')
insert table1(mycol) values('myval')

but in Access I seem to have had to resort to

insert into table1(mycol)
select 'myval' from table1
where (not exists (select * from table1 where mycol='myval'))

this seems to work, but it seems a little bit 'hacky', as I'm not using the from clause at all... anyone know a better way?
 
Hi,



Define an index not allowing duplicated values (which it seems you
already have), and then, just append (try to) the value.... if it is already
there, the append won't be made.


INSERT INTO table1(mycol) VALUES( 'myval' )



Hoping it may help,
Vanderghast, Access MVP


Beeeeeeeeeeeeves said:
HI, hopefully someone has a simple solution to this:
I'm trying to insert a record if it DOESN'T already exist, in one
Click to expand...
statement. I'm not using explicit transactions so one statement equates to
one transaction.
So I want it to be one statement.. in SQL Server, I would do

if not exists (select * from table1 where mycol = 'myval')
insert table1(mycol) values('myval')

but in Access I seem to have had to resort to

insert into table1(mycol)
select 'myval' from table1
where (not exists (select * from table1 where mycol='myval'))

this seems to work, but it seems a little bit 'hacky', as I'm not using
Click to expand...
the from clause at all... anyone know a better way?
 
Yeah, that will work and is in fact what I've done. It does mean I have to
handle an exception though, which through my generic exception logging
policy means the event log fills up a bit faster than I'd like. But since it
*does* represent an exception, i.e. this data flow pattern shouldn't ever
actually happen, I'm happy to accept that it's a minor concern.
 
Back
Top