Just ignore the problems SQL

 
create table when_adding_rows_to_me (col1 integer constraint that_clash_with_the_pk primary key);
create table existing_rows_arent_changed (
  and_i_just_dont_care_that integer, 
  i_need_the_new_rows varchar2(1)
);

insert into existing_rows_arent_changed
  select rownum, 'Y' from dual connect by level <= 10;

insert into when_adding_rows_to_me 
  select and_i_just_dont_care_that from existing_rows_arent_changed;
  
insert into existing_rows_arent_changed values (11, 'Y');

-- error because we insert PK values that already exist
insert into when_adding_rows_to_me 
  select and_i_just_dont_care_that from existing_rows_arent_changed;

-- by adding the hint, we only insert the new row from existing_rows_arent_changed, 
-- silently ignoring the error from the existing values
insert /*+ ignore_row_on_dupkey_index 
  (when_adding_rows_to_me, that_clash_with_the_pk) */ 
into when_adding_rows_to_me 
  select and_i_just_dont_care_that 
  from   existing_rows_arent_changed
  where  i_need_the_new_rows = 'Y';

drop table when_adding_rows_to_me purge;
drop table existing_rows_arent_changed purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)