I am trying to import some tables from Oracle to our SQL Server database with some mixed results.
Things I have tried:
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?
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?