I guess I goofed in the 1st SQL statement. It should be something more like:
INSERT INTO table2 SELECT * FROM table1 WHERE table1.PK NOT IN (SELECT
table2.PK FROM table2)
Your method works, and so does the above. But I am clueless as to which is
faster/more efficient. In the above, the second SELECT needs to be done once
if properly optimized (Is that correct?). In your statement, the SELECT is
performed for each iteration of the INSERT, but retrieves a singleton only.

I did some testing by defining tables1 and 2 as having a primary key and a
non unique field. Table1 has 2 records with the same value for the 2nd
field; table2 is empty. I tried to apply both SQL statements, however using
the 2nd field as the criterion instead of the PK, and to my surprise, only 1
record was inserted in both cases!
So is the SELECT executed for each INSERT iteration?

Regarding the 2nd issue, you are right. The compiler did not accept an INTO
clause inside an EXISTS. Being new to SQL, I find that although I grasped
the concept, many tiny (and major??) details remain. So what's the best way
to know if retrieving a value from a field has failed?

TIA

----- Original Message -----

From: Helen Borrie <helebor@d...>
Date: Mon Jul 2, 2001 12:07 am
Subject: Re: [ib-support] Re: Not in query

No. There is no "EXISTS IN" predicate that I know of. If there were, I
don't
know how it could work. EXISTS returns true if a select returns one or more
rows, otherwise false.

Have you tried this?

insert into table2 select * from table1 where (not (exists(select commonkey
from table2 where commonkey = table1.commonkey)))

Did you try compiling a procedure containing the statement to check whether
(a)
the compiler lets you do it and (b) the :number variable gets the value if
EXISTS() succeeds?

Cheers,
Helen





_________________________________________________________
Do You Yahoo!?
Get your free @... address at http://mail.yahoo.com