Hello MS,

I would like to export all tables from Oracle 11.2 to MS SQL Server 2012 R1.

Using the tool "Microsoft SQL Server Migration Assistant v6.0 for Oracle" did not work for me because there are too many warnings and errors regarding the schema creation (MS cannot know it because they are not the schema designer). My idea is to leave/skip the schema creation to the application designer/supplier and instead concentrate on the Oracle data export and MS SQL data import.

What is the easiest way to export all tables data from Oracle to MS SQL Server quickly?

Is it:

- the MS SQL Import and Export Data Tool
- the MS SQL Integration Services Tool
- not Oracle dump *.dmp format because it is a propritery binary format
- flat file *.csv (delimited format)

Thanks!

• Edited by lingodingo Friday, April 24, 2015 12:38 PM

Easier way would be SQL Server Import and Export because you just need to provide Source, Destination and choose the table and follow the wizard.

(Considering that this is one time migration)

Hi, try and get hold of the Oracle Client software for the Oracle Database; you will need to get the appropriate software that will work with both your Oracle Database version and the SQL Server version.  You should be able to get the appropriate drivers from here: -

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Once installed you will need to review tnsnames.ora in order to provide a connection string to the oracle database.

Take care with the imports as Oracle's default language is US English; so this means that dates are read as MM/DD/YYYY (04/24/2015) as opposed to DD/MM/YYYY (24/04/2015).

Hi lingodingo,

If you want to directly export all tables from Oracle database to SQL Server, I suggest you use SQL Server Import and Export Wizard. Because you just need to follow the wizard with GUI, this is the easiest way.

If you want to make some modification for the tables data before loading to SQL Server, I suggest you use SQL Server Integration Services package. For more details, please refer to the following similar thread:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/38b2bed2-9d4e-48d4-a33f-1d9eed1c062d/flat-file-to-sql-server?forum=sqldatamining

Thanks,
Katherine Xiong