Hello, all:
I'm import data from schema1.table1 (ASSET_NUM,COMPANY_ID,CONSOLI_ID……) to schema2.table2 (ASSET_NUM,COMPANY_ID,CONSOLI_ID……)using SQL script on Oracle 8i. The situation is like this:
In schema1.table1, it does not has a Primary Key and Table1.ASSET_NUM has duplicated records while values in COMPANY_ID are different. For example:
ASSET_NUM COMPANY_ID
---------- ----------
AA237334 AHI
AA237334 DHI
On the other hand, in schema2.table2, the corresponding column Table2.ASSET_NUM is defined as a NOT NULL, Primary Key. These tables' definition can't be modified.
So, I need to drop the duplicated ASSET_NUM/records from schema1.table1 and then import into schema2.table2.
The rule of drop will depend on the priorities of COMPANY_ID (ranking as < Dxx, Axx, Cxx >, from < Keep to Drop >. which means between Axx & Dxx, "Dxx" will be imported. For instances, like the above example, record contains 'AHI' will be dropped. This rule is only depended on the first Character -- substr(company_id, 1,1). )
I thought about:
1/ loop a cursor on whole table1?
2/ just duplicate records and then the rest records will be "insert into table2" directly. Then how do I decide which record to drop? Use a second cursor?
3/ screen all the duplicate record into a temp table and create a PK on that table and then decide… (this sounds very redundant)
Any ideas how can I do this?
Thanks in advance
HelenDo You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
Received on Wed Aug 01 2001 - 15:02:10 CDT
