Migrate data from one database to another database |
|
This topic was created to ilustrate how SQL*XL can be used to migrate data from one database into another database. For example you could want to move an Access database into Oracle. The instructions below are generic so you can use it to port the data from any database type to any other database type
Step 1: dump the table definitions
Connect to the Access database (source database). In the
SQL editor select the tables node. Now right click and choose to describe all
tables. the SQL editor will create all the describe
table statements for you. When you execute the describe statements the table
definitions are in Excel.
describe my_first_table;
describe my_second_table;
...
Step 2: use the dumped information to build the new create table
statements in Excel
You could e.g. dump the table definitions in sheet1
and build the create table statements in sheet2. You have the chance here to
change column names or data types...
The technique used to do this is by using Excel text functions. For example the contactenate function allows you to concatenate pieces of information together. That way you can use the table information in the new create table statements:
=concatenate("create table ",sheet1!A10 )
Step 3: create the tables in Oracle (the
target database)
Select the cells that make up the create table
statements and start the SQL dialog. SQL*XL will see you want to run commands
from the sheet. Make sure you end each create statement with a ;
Step 4: dump all the Access tables
(source tables) into Excel
In the SQL editor, select the tables node and
choose to select from all tables. The SQL editor will create all the select
statements for you:
select * from my_first_table;
select * from my_second_table;
...
Step 5: if you need to fix any data or
column names, do it now in Excel
Use your Excel skills to modify any data
that needs to be modified. You may want to fix column names that you have
changed.
Step 6: use the insert multiple rows
feature to insert the data into Oracle (the target database)
If data
fails to insert, inspect the error message that are left with each failed row.
Depending on the errors you may need to change the table definition and rerun
the process for that table. Note that you can delete a table by running a drop
table command: drop table my_first_table;
See also: