Home » RDBMS Server » Server Utilities » DB Import with same schema but duplicate keys
DB Import with same schema but duplicate keys [message #452039] Mon, 19 April 2010 12:40 Go to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
I have two oracle 9i databases A and B with complete equal schema.
Only the data is different. I want to import all table data from A to B. The problem is that there are duplicate primary keys. Therefore I want to insert data with new primary keys (all referencing tables are concerned too). Are there any existing tools/solutions?

[Updated on: Mon, 19 April 2010 12:41]

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452041 is a reply to message #452039] Mon, 19 April 2010 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Truncate the tables in target databaase before importing.

Regards
Michel
Re: DB Import with same schema but duplicate keys [message #452042 is a reply to message #452041] Mon, 19 April 2010 12:56 Go to previous messageGo to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
but I want to keep the data in database B. Truncate will delete the tables.
Re: DB Import with same schema but duplicate keys [message #452043 is a reply to message #452042] Mon, 19 April 2010 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Import in another schema then use SQL to import in the original one using the sequence of this latter.

Regards
Michel
Re: DB Import with same schema but duplicate keys [message #452159 is a reply to message #452043] Tue, 20 April 2010 07:20 Go to previous messageGo to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
ok, but is there a generic way to do this?

It is a lot of work to update all foreign keys. I am looking for a generic solution.
Re: DB Import with same schema but duplicate keys [message #452160 is a reply to message #452039] Tue, 20 April 2010 07:25 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Design your system so you don't have to re-assign keys on import?

I doubt you'll find any generic solution as most people will avoid ever getting in this mess in the first place.
Re: DB Import with same schema but duplicate keys [message #452161 is a reply to message #452160] Tue, 20 April 2010 07:26 Go to previous messageGo to next message
Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about inserting via a database link? Doing so, you'd have control over what to insert and how.
Re: DB Import with same schema but duplicate keys [message #452162 is a reply to message #452160] Tue, 20 April 2010 07:30 Go to previous messageGo to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
sure, but real life is different from theory Wink
Re: DB Import with same schema but duplicate keys [message #452163 is a reply to message #452039] Tue, 20 April 2010 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
And sometimes in real life you discover that there is a really good reason for the theory - this'd be one of them.

Bottom line, you're going to have to write a lot of SQL to sort the mess out, either after importing into another schema as Michel suggested, or by skipping the import and using DB links as Littlefoot suggested.
Re: DB Import with same schema but duplicate keys [message #452164 is a reply to message #452161] Tue, 20 April 2010 07:37 Go to previous messageGo to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
I think my problem cannot be solved by database links, because this mechanism doesn't solve the problem of duplicate PK's and corresponding FK's
Re: DB Import with same schema but duplicate keys [message #452165 is a reply to message #452159] Tue, 20 April 2010 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
atheis wrote on Tue, 20 April 2010 14:20
ok, but is there a generic way to do this?

It is a lot of work to update all foreign keys. I am looking for a generic solution.

It is a generic way to do it!
In a futur version you can use Data Masking option (which can be used for other things than masking data), it is a paying option.
So you can build a similar product and sell it but you will have nothing for free, as you said there is work to do it.

Regards
Michel
Re: DB Import with same schema but duplicate keys [message #452183 is a reply to message #452163] Tue, 20 April 2010 08:31 Go to previous messageGo to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
yes it's a mess and I am the lucky one who hast to fix everything Mad

ok, I didn't understand what you meant with database links Smile
Now I see that it can be a workaround.

I am just reading something about oracle (advanced) data replication (link see below)

Oracle Data Warehouse can maybe solve the problem too..
Furthermore there is a package called

# DBMS_REPCAT.ADD_DELETE_RESOLUTION - DUP_VAL_ON_INDEX

still investigating Smile


links:
-----------------------
http://www.orafaq.com/wiki/Advanced_Replication_FAQ#What_is_the_difference_between_BASIC_and_ADVANCED_replication.3F
Re: DB Import with same schema but duplicate keys [message #452185 is a reply to message #452165] Tue, 20 April 2010 08:40 Go to previous messageGo to next message
atheis
Messages: 7
Registered: April 2010
Junior Member
What do you mean with future version concerning data masking option? When would it be available?
Re: DB Import with same schema but duplicate keys [message #452200 is a reply to message #452185] Tue, 20 April 2010 10:42 Go to previous message
Michel Cadot
Messages: 67652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Latest 10.2.0.4 with Grid Control and 11g with an API (iirc).
I meant future for you as you are in 9i.

Regards
Michel

[Updated on: Tue, 20 April 2010 10:43]

Report message to a moderator

Previous Topic: Export Schema failed
Next Topic: Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb)
Goto Forum:
  


Current Time: Sun Jan 24 16:23:18 CST 2021