How to Move All tables from one Database into Different Database?

Question

I am using Oracle SQL Developer and I have ten tables. Now I want to move all 10 tables from Oracle into a SQL Server 2008 R2 database. Is there any easy solution for this task?

Answer 1

There are a lot of ways to do this one that go beyond what I listed, but here's a start.

1) You could try the SQL Server Migration Assistant for Oracle. It's free and usually works well for me for smaller or medium sized datasets.

http://www.microsoft.com/en-us/download/details.aspx?id=28766

2) Create a SSIS package.

3) You could create a linked server in SQL and then select * from Oracle into SQL table (messy and probably not a good idea).

http://support.microsoft.com/kb/280106

4) Export to flat files and import into SQL. Again, a ton of caveats with this one.

Answer 2

You can use Data Pump for SQL Server which allows transfering tables from any ADO-compatible data source including Oracle to MS SQL Database. It is not free but can be setup quickly.