Home » SQL & PL/SQL » SQL & PL/SQL » insert into .... select * from.....exception errors
insert into .... select * from.....exception errors [message #199000] Thu, 19 October 2006 10:19 Go to next message
v_piti
Messages: 2
Registered: October 2006
Location: Bucharest
Junior Member

I have the problem:

For the following instruction:

insert into tabel1 select * from tabel2;

If I have 1.000.000 of records which i must insert
from tabel2 into tabel1 and it is an error.
How could i detect where the error appears(at what record?)

For example:
Tabel1 has the records
emp_id
1
2
3
4
there will be a value which will product an error(PK,NOT NULL,FK)
5
6


For the following instruction:

insert into tabel1 select * from tabel2;

It detect an error because of a constraint
My question:
How could I find out that the error appears at the
5th
record.

Best regards,
Vip
Re: insert into .... select * from.....exception errors [message #199006 is a reply to message #199000] Thu, 19 October 2006 10:39 Go to previous messageGo to next message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member


Before insertion spool the file OR

You can use an explicit cursor.Use exception in explicit cursor.
Re: insert into .... select * from.....exception errors [message #199019 is a reply to message #199000] Thu, 19 October 2006 11:59 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
BULK INSERT with SAVE EXCEPTIONS seems to be what you want to use here.

Do note though, that you were incorrect to say that the error appears at the 5th record. There is no such thing as "Nth Record" in a relational database.
Re: insert into .... select * from.....exception errors [message #199044 is a reply to message #199019] Thu, 19 October 2006 20:05 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And if you upgrade to Oracle 10.2, the normal SQL INSERT can log errors.

Ross Leishman
Previous Topic: sql query
Next Topic: Update about 7500 row running long
Goto Forum:
  


Current Time: Sat Dec 10 09:07:04 CST 2016

Total time taken to generate the page: 0.11158 seconds