Re: newbie -- best way to Insert where not exists

The best way is to create your table with a primary key Create table xxx
(var1 type,
var2 type,
var3 type,
constraint pk_name

    primary key (var1, var2) using index);

this will ensure that any attempt to load any two records that have the same values of var1 and var2 will fail.

You will also need to add an error handling scheme to your loading script so that it will simply skip 'duplicate' records and continue loading:

begin
..
..
insert into myTable select 'myValue' from dual ..
..
exception
null;
end;

Good luck,

JC

jimbo1155_at_my-deja.com wrote:

> I'm inserting records in a loop, but it's possible that
> some keys exist already, so I want to code one statement that
> will insert the record only if it doesn't exist.
>
> So I came up with:
>
> insert into myTable select 'myValue' from dual
> where not exists (select * from myTable where mykey = 'myValue')
>
> Does this look like the right way to do this?
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Dec 20 2000 - 16:00:37 CST