Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition for us.

What is the easiest way to get all the data from the entire Oracle Schema into the SQL Server database?

The schema only contains plain old tables and nothing fancy. There might be one or two stored procedures that we'll have no problem migrating by hand.

I could use Oracle's SQLDeveloper to export the table data as CREATE and INSERT statements, but these won't match the Syntax used on SQL Server and I am not looking forward to having to manually fix the syntax errors.

share|improve this question
how much data ? –  miracle173 Sep 6 '12 at 6:09
Hello Martin, would you be interested in a process that uses linked servers? I don't think it answers your whole question, but it will at least give you a way to get your data across. –  Craig Efrein Sep 6 '12 at 6:11
Ha! Linked Servers is certainly something interesting, but if Dietmar's suggestion with the migration assistant works, that'll be even better. –  Martin Sep 6 '12 at 11:35

3 Answers 3

up vote 4 down vote accepted

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do:


However in my case it was not as fast as I would have expected for a 80 GB Oracle-DB (4 hours or something) and I had to do some manual steps afterwards, but the application was developed in hell anyway (one table had 90+ columns and 100+ indices).

share|improve this answer
That certainly sounds good and I guess it shouldn't pose any problems with our data layout and volume. –  Martin Sep 6 '12 at 11:36

Adding to Craig's comment above - I use Linked servers to refresh a few tables in an Oracle database from SQL Server. You can also pull data to SQL Server using OPENQUERY


A couple of links that will help you set up linked server here and here

share|improve this answer

Tring ESF Database Migration Toolkit, you can use it to move data from Oracle to SQL Server or back without any scripts.

share|improve this answer
This is really a link-only comment. Please flesh it out with a little more detail so that viewers of your post have more understanding about your recommendation.. –  RLF 2 days ago
This looks like a thinly veiled advertisement to me. –  mustaccio 2 days ago

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.