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

Helen,
lobolos reasoning is basically that if he/she uses
insert into table2
select * from table1 where (not (exists(select NotAUniqueKey
from table2 where NotAUniqueKey = table1.NotAUniqueKey)))
it will never insert more than one record for any NotAUniqueKey. The
plausible explanation is that since the subselect is executed once for
every row, the content of table2 will have changed before the second
instance of NotAUniqueKey executes. I.e.

if table1 contains

ID NotAUniqueKey
1 2
2 3
3 3

and table2 is empty, then

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

will insert into table2

ID NotAUniqueKey
1 2
2 3

or

ID NotAUniqueKey
1 2
3 3

and not both ID 2 and 3. I have not tested this myself, but it is an
interesting situation I've never thought about and would like it to be
explained from someone like you.

Set