Re: Avoiding Duplicate Inserts

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 21 Nov 2005 05:25:30 -0500
Message-ID: <ArOdnTh3JJwBPRzeRVn-pA@comcast.com>

"raghu" <raghu_vnin_at_yahoo.com> wrote in message news:1132545112.821824.251270_at_g47g2000cwa.googlegroups.com...
> Hi,
>
> I am trying to check if a primary key already exists in a table
> (Oracle 9i) having a few millions of records. Which is the best way to
> check that and avoid duplicate inserts?
>
> Thank you.
>

let oracle do it for you -- just trap the DUP_VAL_ON_INDEX exception (assuming the PK constraint is properly declared)

begin
insert into dept(deptno,dname) values (30,'MARKETING'); exception
when DUP_VAL_ON_INDEX
then null;
end;

if this is a frequesnt issue and you need to do the update if the PK exists, look into the MERGE statement

++ mcs Received on Mon Nov 21 2005 - 04:25:30 CST