Import from Oracle to SQL Server

Toad Data Point
1

I am trying to import some tables from Oracle to our SQL Server database with some mixed results.

Things I have tried:

  1. Export Oracle table to a file (.csv, .xml, Excel, etc…) and then import file to SQL Server. The export works fine; however, the import wizard never lets me specify the datatypes for the new table. It apparently defaults everything to nvarchar(50) even if the data was numeric or dates. I could first create the table (on the SQL Server) with the appropriate data types and then import into an existing table, but this is very tedious for tables with many columns. Is there an easy way to create the necessary DDL based on the Oracle table?

  2. Export to SQL Script with the DDL. This creates a table with the correct data types, but the SQL files get very large with any significant amount of data. TOAD doesn’t seem to do well with a 700MB SQL file. Is there a way to get the DDL without all the insert statements?

Are there any other reasonable ways of doing this?

2

We currently do not have a feature to do this however it is on the list to do.

For now I think the best is to go to the script tab of the table being exported and copy the DDL. Then tweak to common or compatible data types and run in the importing server. At least this is a template that makes the manual process faster.

I will log your request with the other one that is similar. It won’t make the 2.5 release but pretty certain it will make 3.0.

Debbie

3

If you have access to Toad for Oracle, it’s “Create Table” window has an option to load column definitions from an Excel file. It will pick up column names and data types from the spreadsheet…may save you a few minutes.