Home » SQL & PL/SQL » SQL & PL/SQL » Insert statement that ignores some rows
Insert statement that ignores some rows [message #209223] Wed, 13 December 2006 20:25 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I have two tables A and B. Table A contains 100 records
and table B contains 10000. All of Table A records are in
table B .

Normally, I would do
>insert into tabale A select * from table B;

but this will die upon the first violation of unique index.

Is there a way to make the insert statement move on to the
next record ?

Thanks in advance
Re: Insert statement that ignores some rows [message #209224 is a reply to message #209223] Wed, 13 December 2006 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is there a way to make the insert statement move on to the next record ?
Not using vanilla SQL, but could do
WHERE A.PK NOT IN (SELECT B.PK FROM B)
Re: Insert statement that ignores some rows [message #209229 is a reply to message #209224] Wed, 13 December 2006 21:36 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I disagree.

In 10g, you can use the LOG ERRORS command to do just that.
Re: Insert statement that ignores some rows [message #209261 is a reply to message #209229] Thu, 14 December 2006 00:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think that's 10.2 - not available in 10.1.

Merge will also do it nicely in 10g (any release) by not specifying WHEN MATCHED clause.

Ross Leishman

[Updated on: Thu, 14 December 2006 00:30]

Report message to a moderator

Re: Insert statement that ignores some rows [message #209374 is a reply to message #209261] Thu, 14 December 2006 08:16 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Yep, Ross is right, it is in 10gR2. I should have mentioned that.
Previous Topic: send values to IN query with parameter
Next Topic: How to import DUMP file to a new database?
Goto Forum:
  


Current Time: Sat Dec 10 20:39:56 CST 2016

Total time taken to generate the page: 0.07482 seconds