File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes SQL scripts for data migration Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SQL scripts for data migration" Watch "SQL scripts for data migration" New topic
Author

SQL scripts for data migration

Raj Kumar Bindal
Ranch Hand

Joined: Apr 15, 2006
Posts: 418
Hi,

I have to migrate data from one database to another database using sql scripts. So, i need to write sql scripts for the migration. I have table names/depencies available with me for the data that needs to be migrated. Is there anything else that i need to take care while migrating the data? As of now i know there are two ways of writing sql scripts for this:

1. Using database links
2. insert into db2.tablename select * from db1.tablename //not sure if this will work fine.

Please tell me the best way for writing the scripts.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Raj Kumar Bindal wrote:Hi,

I have to migrate data from one database to another database using sql scripts. So, i need to write sql scripts for the migration. I have table names/depencies available with me for the data that needs to be migrated. Is there anything else that i need to take care while migrating the data? As of now i know there are two ways of writing sql scripts for this:

1. Using database links
2. insert into db2.tablename select * from db1.tablename //not sure if this will work fine.

Please tell me the best way for writing the scripts.

What have you described is actually one way. To access another database directly, you need the SQL link, then you could run scripts that would do it. However, the correct syntax to access remote table is table@remote_database.

Anyway, using database links is not trivial. I'd suggest using the data pump export and import tools (expdp/impdp) to copy the data instead, it takes case of grants, constraints and indexes. These tools are not trivial either, but are well described in Oracle's documentation and less things can go wrong with them.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL scripts for data migration
 
Similar Threads
Refactoring Databases: Schema Evolution and Maintaining Data
transfer data from one DB to another
Migrating from Ms Sql Server(T-SQL) to Oracle database 10g(PL-SQL)
Oracle DB Link to SQL Server 2000
i18n