OraFAQ Forum: SQL & PL/SQL » Insert statement that ignores some rows

Home » SQL & PL/SQL » SQL & PL/SQL » Insert statement that ignores some rows

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

Insert statement that ignores some rows [message #209223]

Wed, 13 December 2006 20:25

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

Report message to a moderator

Re: Insert statement that ignores some rows [message #209224 is a reply to message #209223]

Wed, 13 December 2006 20:50

BlackSwan
Messages: 26766
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)

Report message to a moderator

Re: Insert statement that ignores some rows [message #209229 is a reply to message #209224]

Wed, 13 December 2006 21:36

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I disagree.

In 10g, you can use the LOG ERRORS command to do just that.

Report message to a moderator

Re: Insert statement that ignores some rows [message #209261 is a reply to message #209229]

Thu, 14 December 2006 00:28

rleishman
Messages: 3728
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

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Yep, Ross is right, it is in 10gR2. I should have mentioned that.

Report message to a moderator

Previous Topic:	send values to IN query with parameter
Next Topic:	How to import DUMP file to a new database?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 13 05:53:19 CST 2024