SQL*XL online help

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: