|Subject||Re: [ib-support] Re: Not in query|
|Author||Svein Erling Tysvær|
>I guess I goofed in the 1st SQL statement. It should be something more like:I think Helen's suggestion will be much faster (she is one of the big
>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.
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 aSorry, I don't understand what you are saying here. Unless you have 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?
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?