Re: SQL Insert Question

Phil Cook wrote:
>
> This query does not filter out the records that already exist, and
> I get primary key constraint violated. I broke the query into two
> PL/SQL sections to get it to work. Thanks in advance....
>
> insert into table1
> select a.primary_key from table2 a where not exists
> ( select * from table1 b
> where a.primary_key = b.primary_key );

I assume you really meant:

INSERT INTO table1
SELECT * FROM table2 a
WHERE NOT EXISTS (SELECT 'X' FROM table1 b

    WHERE a.primary_key = b.primary_key);

where table1 and table2 have identical columns in the same order.

If so, I cannot understand the source of your duplicate key in table1 unless, maybe, a similar task is running somewhere else. This could possibly allow a row to be added to table1 from another source after the kernel has evaluated the SELECT, but I don't think so.

What are the "two PL/SQL sections" that work?

Curiouser and Curiouser,
Gary Received on Sat Sep 20 1997 - 00:00:00 CDT