Home » SQL & PL/SQL » SQL & PL/SQL » load data from one table to another table avoiding duplicate data
load data from one table to another table avoiding duplicate data [message #329862] Thu, 26 June 2008 16:06 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I am getting flat file and loading data into temp table and then needs to load this data into another table si new table have correct data means i can remove the duplicate data and other unncessarry data.

My temp table is like:


ID VARCHAR2(18)
DESC VARCHAR2(40)
C_FLAG VARCHAR2(1)
E_FLAG VARCHAR2(1)
L_PRICE NUMBER(11,3)
UOM VARCHAR2(3)
DIV VARCHAR2(2)
B_UOM VARCHAR2(3)
E_CAT VARCHAR2(2)
E_UPC VARCHAR2(18)
G_WGHT VARCHAR2(13)
wUNIT2A VARCHAR2(3)
VOL VARCHAR2(13)
volUnit2A VARCHAR2(3)
L1 VARCHAR2(13)
W1 VARCHAR2(13)
H1 VARCHAR2(13)
UOD VARCHAR2(3)
A_UOM VARCHAR2(3)
N_CNV VARCHAR2(5)
D_CNV VARCHAR2(5)
E_CAT2 VARCHAR2(2)
E_UPC2 VARCHAR2(18)
G_WGHT2 VARCHAR2(13)
W_UNIT2 VARCHAR2(3)
VOL2 VARCHAR2(13)
VOL_UNIT2 VARCHAR2(3)
L2 VARCHAR2(13)
W2 VARCHAR2(13)
H2 VARCHAR2(13)
UOD2 VARCHAR2(3)
S_ORG VARCHAR2(4)
D_CH VARCHAR2(2)
MIN_QTY VARCHAR2(13)
P_ID VARCHAR2(4)
Desh VARCHAR2(3)

and i have same structure of my real table, only real table has composite key - (ID, A_UOM, D_CH, S_ORG, DIV)
When i am trying to insert data from temp table to real table, its not laoding becuase of unique contraint violation.
How can i handle in insert sql?
I know there is someting rownum and partition but didn't know how put into sql.
Could you please guide me with an example?

Thanks?
Re: load data from one table to another table avoiding duplicate data [message #329863 is a reply to message #329862] Thu, 26 June 2008 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
poratips,
As an Senior Member who consistently ignores Posting Guidelines, you deserve to be ignored.

http://www.orafaq.com/forum/t/88153/0/



Re: load data from one table to another table avoiding duplicate data [message #329864 is a reply to message #329862] Thu, 26 June 2008 16:24 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
I am really sorry as totally forgot to format while rushing.
REsubmitting, please pardon me.
Hi,
I am getting flat file and loading data into temp table and then needs to load this data into another table si new table have correct data means i can remove the duplicate data and other unncessarry data.

My temp table is like:

ID VARCHAR2(18)
DESC VARCHAR2(40)
C_FLAG VARCHAR2(1)
E_FLAG VARCHAR2(1)
L_PRICE NUMBER(11,3)
UOM VARCHAR2(3)
DIV VARCHAR2(2)
B_UOM VARCHAR2(3)
E_CAT VARCHAR2(2)
E_UPC VARCHAR2(18)
G_WGHT VARCHAR2(13)
wUNIT2A VARCHAR2(3)
VOL VARCHAR2(13)
volUnit2A VARCHAR2(3)
L1 VARCHAR2(13)
W1 VARCHAR2(13)
H1 VARCHAR2(13)
UOD VARCHAR2(3)
A_UOM VARCHAR2(3)
N_CNV VARCHAR2(5)
D_CNV VARCHAR2(5)
E_CAT2 VARCHAR2(2)
E_UPC2 VARCHAR2(18)
G_WGHT2 VARCHAR2(13)
W_UNIT2 VARCHAR2(3)
VOL2 VARCHAR2(13)
VOL_UNIT2 VARCHAR2(3)
L2 VARCHAR2(13)
W2 VARCHAR2(13)
H2 VARCHAR2(13)
UOD2 VARCHAR2(3)
S_ORG VARCHAR2(4)
D_CH VARCHAR2(2)
MIN_QTY VARCHAR2(13)
P_ID VARCHAR2(4)
Desh VARCHAR2(3)

and i have same structure of my real table, only real table has composite key - (ID, A_UOM, D_CH, S_ORG, DIV)
When i am trying to insert data from temp table to real table, its not laoding becuase of unique contraint violation.
How can i handle in insert sql?
I know there is someting rownum and partition but didn't know how put into sql.
Could you please guide me with an example?

Thanks,
poratips
Re: load data from one table to another table avoiding duplicate data [message #329901 is a reply to message #329864] Fri, 27 June 2008 00:40 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

You can achieve this iether by writing PL/SQL block, or by grouping all columns to discard duplicates, then you can insert into live table.

Thanx and Regards

MSMallya
Re: load data from one table to another table avoiding duplicate data [message #329903 is a reply to message #329862] Fri, 27 June 2008 00:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Which presumes facts not presently substantiated .
Re: load data from one table to another table avoiding duplicate data [message #329951 is a reply to message #329864] Fri, 27 June 2008 02:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This code should only insert rows who's key values are not already in the real table.

INSERT INTO real_table (<column list>)
(SELECT <column_list> FROM temp_table
 WHERE (ID, A_UOM, D_CH, S_ORG, DIV) NOT IN (SELECT ID, A_UOM, D_CH, S_ORG, DIV FROM real_table);
Re: load data from one table to another table avoiding duplicate data [message #330023 is a reply to message #329951] Fri, 27 June 2008 06:22 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks for providing insert sql.
I am still getting Unique constraint error so Insert got failed.

ORA-00001: unique constraint (T_PK) violated


My table has more than one reocords.
ex.My temp table contains same kind of four records but i need to insert only one records, let say first record.
so i will load each time, only one record-very first record so it willnot throw any unique constraint error.

Thanks,





Re: load data from one table to another table avoiding duplicate data [message #330026 is a reply to message #330023] Fri, 27 June 2008 06:28 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
You can try using Distinct function or use rowid to avoid duplicates
Re: load data from one table to another table avoiding duplicate data [message #330034 is a reply to message #330026] Fri, 27 June 2008 07:08 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Menon.
I was trying use rownum and partition by clause but didn't get proper syntax, if yo ucan provide me, pelase?

I have following records in the table/incoming file.
ID A_UOM D_CH S_ORG DIV P_ID 
1 U 1 01 A P1
1 U 1 01 A P2
1 U 1 01 A P3
1 U 1 01 A P4

2 U 1 02 B P1
2 U 1 02 B P2
2 U 1 02 B P3
2 U 1 02 B P4

So its only differ by p_id and i want to load only one record so i can grab the first record and insert into real/target table.
How can i handle this with rowid?

thanks,
Re: load data from one table to another table avoiding duplicate data [message #330064 is a reply to message #329862] Fri, 27 June 2008 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Poratips,

Please enroll in remedial CUT & PASTE training so the data aligns with column headers.

>let say first record.
In any RDBMS rows in a table have NO inherent order.
Rows in a table are like balls in a basket.
Which red ball is the "first" red ball in the basket?
Re: load data from one table to another table avoiding duplicate data [message #330077 is a reply to message #330064] Fri, 27 June 2008 09:29 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Sorry about it.
Thanks.
Right, oracle don't store/fetch data in order but let say if i have to get only 1 record out of 4 reocrds, how can i use row_num over partition by clause?

Thanks for your help.

Re: load data from one table to another table avoiding duplicate data [message #330080 is a reply to message #330077] Fri, 27 June 2008 09:45 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
row_num will help you if you want a group of records from between the table of a record at the nth place.

Which P_ID would you want? if you are okay with P_ID P1 for every record then take that column out and use rowid for the rest of the columns then you can put the same P_ID for the inserted columns

If you search this forum on rowid then will you get how to use it to remove duplicates.

I cannot spoon feed you unless I want to get fried by others... Smile
Re: load data from one table to another table avoiding duplicate data [message #330085 is a reply to message #329951] Fri, 27 June 2008 09:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the problem is that you've got multiple rows in your temp table with the same values of (ID, A_UOM, D_CH, S_ORG, DIV), you could try this:
INSERT INTO real_table (<column list>)
(SELECT <column_list> 
 FROM (SELECT <column_list>
             ,row_number() over (partition by ID, A_UOM, D_CH, S_ORG, DIV order by <some columns>) rnum
       FROM temp_table
       WHERE (ID, A_UOM, D_CH, S_ORG, DIV) NOT IN (SELECT ID, A_UOM, D_CH, S_ORG, DIV FROM real_table)
 WHERE rnum=1);
where <some columns> are a set of columns that you can use to identify which of the rows you want to come first.

I'm making no promises about the performance....
Re: load data from one table to another table avoiding duplicate data [message #330095 is a reply to message #330034] Fri, 27 June 2008 10:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Since you didn't mentioned about the oracle version I assumed you will be in Oracle 10g or above and Also you have mentioned you just want only any one record in the group you could try the error logging clause. Check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBDIGAH

Regards

Raj

[Updated on: Fri, 27 June 2008 10:19]

Report message to a moderator

Re: load data from one table to another table avoiding duplicate data [message #330100 is a reply to message #330080] Fri, 27 June 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
durgadas.menon wrote on Fri, 27 June 2008 16:45
I cannot spoon feed you unless I want to get fried by others... Smile


Laughing


Re: load data from one table to another table avoiding duplicate data [message #333181 is a reply to message #330100] Thu, 10 July 2008 13:31 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Previous Topic: How to build Consolidate Results from multiple rows
Next Topic: running multiple packages in database
Goto Forum:
  


Current Time: Sun Dec 11 08:01:23 CST 2016

Total time taken to generate the page: 0.09136 seconds