Move Data from Oracle to SQL Server

Question

I would like to copy parts of an Oracle DB to a SQL Server DB. I need to move the data because the Oracle box is being decommissioned. I only need the data for reference purposes so don't need indexes or stored procedures or contstaints, etc. All I need is the data.

I have a link to the Oracle DB in SQL Server. I have tested the following query, which seemed to work just fine:

select * into NewTableName
from linkedserver.OracleTable

I was wondering if there are any potential issues with using this approach?

Answer 1

The only issue I see with that is you will need to execute that of course for each and every table you need. Glad you are decommissioning the oracle server :-). Otherwise if you are not concerned with indexes or any of the existing sprocs I don't see any issue in what you are doing.

Answer 2

Using SSIS (sql integration services) may be a good alternative especially if your table names are the same on both servers. Use the import wizard via and it should create the destination tables for you and let you edit any mappings.

Answer 3

A faster and easier approach might be to use the Data Transformation Services, depending on the number of objects you're trying to copy over.

Answer 4

The "select " approach could be very slow if tables are large. Consider writing pro*C in that case or use Fastreader http://www.wisdomforce.com/products-FastReader.html

Answer 5

There are tools that can do this automatically for you. Check out Data Moving Tool: http://www.sersoftware.com/prod/data-moving-tool-overview.php