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?

Answer 1:

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.

Answer 2:

  try
  ... where (key_code not in (select key_code from table1));