Tired of large inserts failing due to one row? Try this: SQL

 


-- setup
create table i_want_to_keep (
the_rows_that_are_valid integer not null primary key,
and_ignore_the_failures_when_i integer not null
);

create table dodgy_data_so (
duplicate_key_values integer,
and_null_values integer
);

insert into dodgy_data_so
select mod(level, 5), case when mod(level, 10) in (1, 2) then null else level end
from dual
connect by level <= 10; commit; -- we need to create the error logging table -- this doesn't appear "magically" exec dbms_errlog.create_error_log(dml_table_name => 'i_want_to_keep', err_log_table_name => 'my_error_table');

-- have a look at what the error logging looks like
desc my_error_table

insert into i_want_to_keep (
the_rows_that_are_valid,
and_ignore_the_failures_when_i
)
select duplicate_key_values, and_null_values
from dodgy_data_so
log errors into my_error_table
reject limit unlimited
/

-- only half the data was inserted
select * from i_want_to_keep
order by 1;

-- have a look at the data that got rejected
col ora_err_mesg$ format a100
col the_rows_that_are_valid format a10
col and_ignore_the_failures_when_i format a10
select ORA_ERR_MESG$, the_rows_that_are_valid,and_ignore_the_failures_when_i
from my_error_table
order by 2;

drop table my_error_table purge;
drop table i_want_to_keep purge;
drop table dodgy_data_so 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)