How to transfer data from Oracle to SQL Server 2000

[rubansilvester]

Hi,

How can i transfer data from Oracle to SQL Server 2000 other than using DTS.

Plz do reply me

Thanx in advance

Ruban

(ruban.silvester@majoris.com)

 

[tlbroadbent]

Some ideas that come to mind follow. I'm sure there are other methods in addition to these.[ol][li]You can export the data from Oracle to flat files and import to SQL Server using BCP or Bulk Insert.

[li]You can add the Oracle Sever as a linked Server in SQL Server and transfer data through T-SQL statements.

[li]You can link the Oracle and SQL tables to an Access database and use Access queries to transfer the data.

[li]You can write a program in a language of your choice to read Oracle data and write it to SQL Server.[/ol]Why the aversion to DTS? It is easy to use, flexible and speedy. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[randee]

Hey all,

I would like to do something similir.

We would like to replicate information on an Informix server to Microsoft SQL server. Does anyone have any suggestions on how to do it? The information needs to be updated throughout the day.

Any info on, Third Party tools and DMO code would be helpful.

Any other ways to do it, would also be appreciated.

Randy

 

[MinAmiChief]

rubansilvester,
Personally I like OPENROWSET. But Terry Broadbent has convinced me that DTS is a handy and powerful tool. Now I use it with dtsrun console app. that rename's the input file to the name that the pre-compiled DTS package expects. Import with dtsrun. then rename the file to its original name.

Back to openrowset.
From BOL:
Includes all connection information necessary to access remote data from an OLE DB data source.

It allows me tow write a simple select query and then its as if the table was in SQL Server. It can also be use as a target for INSERT,UPDATE and DELETE.

Be the change that you want to see in the world - Mahatma Gandhi