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

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