OraFAQ Forum: SQL & PL/SQL » Insert: ignoring errors

Home » SQL & PL/SQL » SQL & PL/SQL » Insert: ignoring errors

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insert: ignoring errors [message #21473]

Tue, 06 August 2002 08:45

Kevin Runde
Messages: 1
Registered: August 2002

Junior Member

How do I ignore rows that will violate constraints when doing a batch insert?

Thanks,
Kevin

Report message to a moderator

Re: Insert: ignoring errors [message #21474 is a reply to message #21473]

Tue, 06 August 2002 09:28

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

One option is a cursor for loop:

for r in (select * from source_table) loop
  begin
    insert into target_table
      values (r.col1, r.col2, ...);
  exception
    when others then
      null;
  end;
end loop;

If you are dealing with a PK constraint, you can still use an INSERT/SELECT construct:

insert into target_table
  select * from source_table s
   where not exists
     (select null
        from target_table t
       where t.pk = s.pk);

You could also use this approach with FKs - except you would instead use EXISTS on the lookup table(s) to make sure the values were present.

Report message to a moderator

Re: Insert: ignoring errors [message #21476 is a reply to message #21473]

Tue, 06 August 2002 11:17

Aaron
Messages: 13
Registered: January 2002

Junior Member

Have you tried using the SQL Loader program to do this data load? For the simple fact that if the data source does voliate a constraint the row is placed into a bad file which can be view after the load is complete, and the row would be skipped and the rest of the data would be inserted as well

Report message to a moderator

Re: Insert: ignoring errors [message #21493 is a reply to message #21473]

Wed, 07 August 2002 22:26

gary
Messages: 35
Registered: January 2001

Member

If you can actually amend the constraint on the table, the EXCEPTIONS INTO clause might fit the bill

(EG ALTER TABLE FRED MODIFY CONSTRAINT BILL EXCEPTIONS INTO DUMP)

Then drop or truncate the dump table at the end (and modify the constraint back to its initial state).

Report message to a moderator

Previous Topic:	week of year calc
Next Topic:	PL/SQL & SQLPLUS Questions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:08:04 CDT 2024