copying data to SQL Server from an Oracle database

What is the most straightforward way to copy tables and views from an oracle database into a sql server 2005 database?

thanks for any suggestions.

Marilyn

Mount your Oracle server and database as a linked database in your SQLServer environment. Then perform a standard insert-select (or select into).

If you also must make data transformations you should use DTS (or what's the name nowadays is).

Best regards

Nils Nordenbrink

|||

Probably the easiest method is to use the SQL Server 2005 Import and Export Wizard. Create the database you want to hold the Oracle data in SQL Server 2005. Right click on the database from Management Studio, select Tasks, Import Data. Click Next on the first Wizard dialog. Choose either .Net Framework Data Provider for Oracle or Oracle Provider for OLEDB. Proceeding further with the wizard should allow you to select the data of interest.

Best of luck!

|||

hi steve,

i'm using sql server express edition and have the same problem, however the "import data" task is not available on this version.

are you aware of any other options for express edition?

this is my current set up:

sql server 2005 express edition,
sql server management studio express,
oracle tables (source data currently linked through odbc)
adp database file (new db where i'm trying to import the oracle tables)

i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.

what is the fastest option? some of these tables hold over 1m records.

i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.

many thanks
luzippu

|||

It really depends on your purpose for making a copy of 1m record tables. That is something that should be avoided if possible. For a one-time shot ease of use is the prime consideration, even if it take a (little) longer. But to do this every week or every day is not a great design.

DTS is essentially the SQL Server 8.0 version of the "import data" functionality and if you have SQL Server 8.0 you might try that option.

If your primary purpose is simply to back up the data, then you might just use a backup process which will probably be much faster. If the primary purpose is mainly to query the data, then linked servers are probably what you want - no copy required, just link the Oracle server and execute your query in SQL Server against the Oracle data. This is the "fastest" option for occasional queries or to join data in SQL Server. But of course each query to Oracle will transfer the result over the wire to SQL Server, which is not the best for high performance applications that are doing many large queries repetitively. In that case copying the data once will then allow better performace as the application runs, at the expense of the copy and the issue of "aging" of the data. With the linked server the data is accurate at the time of the query.

One suggestion would be simply to upgrade your server if the import data functionality is not supported on SQL Express. Spending a bunch of time trying to create alternative workarounds with associated maintenance is probably not really cost effective.