Re: Testing for Existence of Rows - What's Fastest?

May be you want to consider NOT inserting the value in the first place if the condition is (not) satisfied.
I mean something like:

Insert into TableA (<field_list>)
values (<field values list>)
where not exists( select <something> from TableA where <your where clause>)

Then you could check the value SQL%FOUND (or SQL%NOTFOUND, whichever you prefer) to know if you actually inserted one record or none. And if you still want to raise an exeception, you are free to do so.

I think this is just another way of looking at the problem, I don't really know if you prefer to insert the value and then rollback or to avoid it altogether.

I use statements like this all the time to avoid raising unnecessary exceptions for duplicate records when I am sure that getting rid of the duplicate is better than stopping the application.

Bye,
Flavio Received on Sun May 26 2002 - 16:45:03 CDT