Home » SQL & PL/SQL » SQL & PL/SQL » I am facing a problem during INSERT operation its giving Unique Constraint Error.
I am facing a problem during INSERT operation its giving Unique Constraint Error. [message #383258] Wed, 28 January 2009 00:49 Go to next message
natrajdreams
Messages: 10
Registered: January 2009
Junior Member
Hi,

I am facing a problem during INSERT operation its giving Unique Constraint Error.

I want tdo the following:

INSERT INTO TABLEB(COL1,COL2)
SELECT COL1,COL2 FROM TABLEA

How to get rid of the duplicate rows while inserting so that if there 22 rows and of those 3 are duplicates it will insert 19 rows into TABLEB

Any help will be needful
Re: I am facing a problem during INSERT operation its giving Unique Constraint Error. [message #383261 is a reply to message #383258] Wed, 28 January 2009 00:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>How to get rid of the duplicate rows while inserting so that if there 22 rows and of those 3 are duplicates it will insert 19 rows into TABLEB

Do not INSERT the 3 duplicate records!
Re: I am facing a problem during INSERT operation its giving Unique Constraint Error. [message #383262 is a reply to message #383258] Wed, 28 January 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Finding or deleting duplicates is a FAQ.
Please search before posting.

Regards
Michel
Re: I am facing a problem during INSERT operation its giving Unique Constraint Error. [message #383284 is a reply to message #383258] Wed, 28 January 2009 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you are lucky, DISTINCT might help:
insert into table_b (col1, col2)
  select DISTINCT col1, col2 from table_a
Re: I am facing a problem during INSERT operation its giving Unique Constraint Error. [message #383301 is a reply to message #383258] Wed, 28 January 2009 02:55 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
natrajdreams wrote on Wed, 28 January 2009 07:49
I am facing a problem during INSERT operation its giving Unique Constraint Error.

I want tdo the following:

INSERT INTO TABLEB(COL1,COL2)
SELECT COL1,COL2 FROM TABLEA

How to get rid of the duplicate rows while inserting so that if there 22 rows and of those 3 are duplicates it will insert 19 rows into TABLEB

It is your data. You know the column(s) this unique constraint is built on; but you did not find important to post it. Also stating "duplicates" is also related to certain column(s) - as you did not specify them, Littlefoot understood it as duplicate in all its columns (which is probably not right according to http://forums.oracle.com/forums/thread.jspa?threadID=852288&tstart=0).

It is your data. You should know, which rows should be inserted and which should not.

Just an example: let's say, that COL1 is unique, both columns are numeric and you want to insert following rows:
COL1 COL2
---- ----
   1    2
   1    5
   1    1

Which row (only one can be inserted without breaking unique constraint) should be inserted and why?
Previous Topic: what are tk profile user and stausback in oracle
Next Topic: Triggers fail to fire (merged 3)
Goto Forum:
  


Current Time: Thu Dec 08 00:20:16 CST 2016

Total time taken to generate the page: 0.05151 seconds