Re: How to insert only records that don't already exist?

Jerry:

One thing I noticed about this MINUS approach is that it appears that the group of columns being inserted must match the group of columns being prepared. In my situation, the incoming records have multiple columns, but I only care about comparing the primary key. I'm not seeing a way to do that with MINUS; am I missing something?

Thanks again.

Jerry Gitomer <jgitomer_at_erols.com> wrote in message news:953359234.593356785_at_news.erols.com...
> On Fri, 17 Mar 2000, John Haskins wrote:
> >>Greetings, all:
> >
> >Can anyone tell me how to copy records from one table to another, without
the command aborting when 1 or more of the records being copied already exists in the destination table?
> >
> >The scenario: periodically I get updates of certain reference tables, in
which perhaps 10% of the records are actually new since the last update. I'd like to insert just the new records into the appropriate tables. I've used INSERT INTO to accomplish this in the past, and it works great when the incoming update records don't violate any uniqueness constraints with the existing records. But when they do, the INSERT INTO fails completely, keeping not only the old records but also the new records from being copied to the destination table.
> >
> >I've tried to filter the update records by comparing them with those
already in the destination table. While this seems like a good approach in theory, my execution of it has failed. The code is:
> >
> >insert into table1
> > select key_code, key_name
> > from table2
> > where (
> > select count(*) from table1
> > where table1.key_code = table2.key_code
> > ) = 0
> >;
> >
> >
> >Can anyone point out how to execute this correctly?
> >
> >Thanks.
> >
>
> Try:
>
> insert into table1
> (select * from table2 minus select * from table1);
>
>
> You will find the minus operation is very fast and will return only those
 rows
> that are not already in table1.
>
>
>
> --
> Jerry Gitomer
> Once I learned how to spell DBA, I became one
>
Received on Mon Mar 20 2000 - 00:00:00 CST