Best way to move data from SQL Server to Oracle
How would I pull data from tables in a SQL Server database into an Oracle database? Do I need to use an ODBC connection, move the data into flat files, and then use SQL*Loader to populate the tables in the Oracle database?
Or is there an easier way?
If you are going to need this data moved on a regular basis, then I'd
investigate using Oracle's Heterogeneous Services (HS) to establish the
connectivity to SQL using HS's generic connectivity via ODBC drivers.
If using HS isn't an option for you or you can't handle the limitations of
that solution, or if you just need to move data one time, then:
-- investigate generation of a flat file (using isql's bcopy feature probably)
that could be loaded with SQL*Loader on Oracle.
-- investigate using an ODBC driver to connect to Oracle and SQL at the same
time via a front-end like MS Access or similar tool and transfer the data
directly using that tool.
This was first published in June 2004