load data from one table to another table avoiding duplicate data [message #329862] |
Thu, 26 June 2008 16:06  |
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 #329864 is a reply to message #329862] |
Thu, 26 June 2008 16:24   |
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 #329951 is a reply to message #329864] |
Fri, 27 June 2008 02:22   |
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   |
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 #330034 is a reply to message #330026] |
Fri, 27 June 2008 07:08   |
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 #330085 is a reply to message #329951] |
Fri, 27 June 2008 09:49   |
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....
|
|
|
|
|
|