how to migrate data from database A to database B [message #633654] |
Tue, 24 February 2015 00:55 |
|
arsia
Messages: 3 Registered: February 2015 Location: Australia
|
Junior Member |
|
|
Hi every one
i have database A that working 1 year , now i want transfer data of database A to database B (database B is including structure only) but database A and B have some differences between tables.
some tables have less column and some have more.second issue is in database B the structure maybe normalized (resulting to have more tables) whereas in Database A all fields are stored in the same table.
how can i migrate data from database A to B ?
Thanks
|
|
|
Re: how to migrate data from database A to database B [message #633656 is a reply to message #633654] |
Tue, 24 February 2015 01:27 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Regarding differences you mentioned, it looks like a manual job, i.e.
insert into some_table@database_B (col1, col2, col3, ...)
select col1, col34, col2, ...
from some_table@database_A
What do you call a "database"? Is it, by any chance, a "schema"? That would make things simpler and faster (you wouldn't need a database link).
|
|
|
|
Re: how to migrate data from database A to database B [message #633659 is a reply to message #633657] |
Tue, 24 February 2015 02:01 |
|
arsia
Messages: 3 Registered: February 2015 Location: Australia
|
Junior Member |
|
|
Quote:The question is: what do you want to do with the current tables and data in database B? Keep or leave?
i understand your question but i dont understand the reason that u ask such a question.however i want terminate the old database (database A) and use the new database (database B) this because of upgrade and deploying software that working with new structure of database .
[Updated on: Tue, 24 February 2015 02:02] Report message to a moderator
|
|
|
Re: how to migrate data from database A to database B [message #633660 is a reply to message #633656] |
Tue, 24 February 2015 02:04 |
|
arsia
Messages: 3 Registered: February 2015 Location: Australia
|
Junior Member |
|
|
Quote:Regarding differences you mentioned, it looks like a manual job, i.e.
insert into some_table@database_B (col1, col2, col3, ...)
select col1, col34, col2, ...
from some_table@database_A
What do you call a "database"? Is it, by any chance, a "schema"? That would make things simpler and faster (you wouldn't need a database link).
thanks i think you are right and this is the only way
|
|
|
Re: how to migrate data from database A to database B [message #633661 is a reply to message #633659] |
Tue, 24 February 2015 02:08 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
arsia wrote on Tue, 24 February 2015 09:01...
i understand your question but i dont understand the reason that u ask such a question...
The purpose is that if you don't want to keep the tables and data in database B then you can just export/import those from database A.
This is faster than many INSERT SELECT across a db link and keep the source constraints, indexes and so on.
[Updated on: Tue, 24 February 2015 02:09] Report message to a moderator
|
|
|
Re: how to migrate data from database A to database B [message #633762 is a reply to message #633661] |
Tue, 24 February 2015 08:54 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
You might find better results exporting DB A and importing to DB B remapping schemas just to get the data in, depending on how complex the remappings are. The optimizer has a bad habit of making a mess of queries over DB links. If you stuff it all in the same database temporarily, you might get better net performance. You'll have to test.
|
|
|
|