Home » SQL & PL/SQL » SQL & PL/SQL » query:move data from one database to another
query:move data from one database to another [message #234929] Thu, 03 May 2007 08:40 Go to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
hi experts,
we are developing a new system for our concern with a new database named ORADB..and we have created all tables under a schema named TRUCK
we are already using a database named SIT..
we have got all our master tables in SIT...
now, we have to move the data of the master table like customer,vendor,employees etc from SIT to our new database ORADB...

master tables in the SIT (old database) holds many unnecessary information..but we have designed our new master tables in OraDb to store only relavant..
please help me out by giving your valuable suggestions to copy data from old database to new one...

thanks in advance
Re: query:move data from one database to another [message #234968 is a reply to message #234929] Thu, 03 May 2007 10:16 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
try below option

1.export data with query clause and import with ignore=y
Note: if table structure is same.

2.CTAS with where clause and export / import with ignore=y

3.Unload data from source database through spool option / load through SQL*LOADER/EXTERNAL TABLE option

hope this helps
Taj

[Updated on: Thu, 03 May 2007 10:17]

Report message to a moderator

Re: query:move data from one database to another [message #235376 is a reply to message #234968] Sat, 05 May 2007 07:29 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks ,
two of my tables have different structures..
my new database table wil be taking different values from different tables.
can u explain me stepwise.


thanks in advance
Re: query:move data from one database to another [message #235382 is a reply to message #234929] Sat, 05 May 2007 07:55 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
You did not mention your oracle version, but if you are on 10g, use data pump. That's the best tool there is for transportation of data.
Re: query:move data from one database to another [message #235383 is a reply to message #235382] Sat, 05 May 2007 08:01 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks,
i am using oracle 9i..
i want my master tables to get copied to my new database..please help me out
Re: query:move data from one database to another [message #235387 is a reply to message #235383] Sat, 05 May 2007 08:09 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
If you are not comfortable with export/import then you can recreate all of the master tables in the target database using Create table as select (CTAS) command.
Hope this helps
Re: query:move data from one database to another [message #235460 is a reply to message #235387] Sun, 06 May 2007 06:30 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks....
the problem is the tablesare already created.. some amount new data are entered manualy..is there any other way...can u please help me outt...
Re: query:move data from one database to another [message #235463 is a reply to message #235460] Sun, 06 May 2007 07:28 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

Already data is feeded then you should use SQL*LOADER option load your data.

Regards
Taj
Previous Topic: v$ view
Next Topic: login with hyperlink?
Goto Forum:
  


Current Time: Sat Dec 10 14:28:18 CST 2016

Total time taken to generate the page: 0.08924 seconds