At 12:54 PM 03-07-01 +0300, you wrote:

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

Huh? seems to sum up my response to this bit of reasoning (unless I'm confused and you're not talking about this:
insert into table2 select * from table1 where (not (exists(select commonkey from table2 where commonkey = table1.commonkey)))

This cannot recurse, even if you had duplicates for commonkey in table1 (which you wouldn't because, in your original requirement, you wanted to duplicate from one table to a matching other by testing the primary key!)

The "secondary query" that you refer to (it's a correlated sub-query) is very cheap, because it uses the index on the primary key and doesn't return a dataset, just True or False based on ripping straight through the tree.

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

Hmmmm....interrrrresting....

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________