1- I know Helen is one of the best. This is why I enjoy reading her postings
:-)

2- The problem is in that the secondary query is performed on each
iteration, and thus if 2 records have the same field value, the first record
will be added, but the 2nd will not because when the secondary query is
executed again, it will find that value in table2. This is similar to the
case of infinite recursive insertion (our case is finite non insertions
though) which the following statement suffers from:
INSERT INTO table1 SELECT * FROM table1
The above statement leads to infinite recursive insertion. (I have not
tested this, but it is from Claudio Valderrama's website, and I take his
word for granted)
And just for info, a simple SELECT shows indeed both records, and i am not
committing the transactions as i am running the queries interactively from
IBConsole. I just commit at the end of the test. In fact, the only
disadvantage might be a nanosecond slowdown that non committed transaction
overhead imposes.

Regards

----- Original Message -----
Date: Mon, 02 Jul 2001 12:17:35 +0200
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: Re: Re: Not in query

>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 think Helen's suggestion will be much faster (she is one of the big
guru's and normally knows what she is writing about). Although executing
the second select once would suffice in your example, this select could
have a where clause depending on the current row - and I think IB executes
it for every iteration.

>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?

Sorry, I don't understand what you are saying here. Unless you have a
SELECT DISTINCT it should retrieve all rows matching your criteria. Maybe
there is some kind of a rounding issue? Do you get two rows if you do a
simple select? Did you commit your transaction?

HTH,
Set





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