16

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.

Improve this question
0

2 Answers 2

Reset to default
13

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

SQL Server Migration Assistant for Oracle (documentation)
Microsoft SQL Server Migration Assistant v6.0 for Oracle (download)
SQL Server Migration Assistant (SSMA) Team's Blog

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).

Improve this answer
1
  • it shown errors for Oracle sample DB
    – Serg Kryvonos
    Jan 27, 2017 at 15:12
7

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

INSERT INTO dbo.SQLTable1
  SELECT * FROM OPENQUERY(ORALINKEDSERV, 'SELECT * FROM OracleTable1')

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

Improve this answer
1
  • One comment on using linked servers to Oracle. I just had a bad experience with linked server to a large Oracle table with some CLOB data. If I pulled over a few rows, it worked fine. When copying the entire table, rows would get switched around! For example, original data was "123", "abcdef" "345", "xyz" What we ended up with was "123", "xyz" "345", "abcdef"
    – J Brun
    May 21, 2020 at 17:01

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