CODEWITHSUNDEEP
oracle-databaseimportodbcscheduled-tasks
Tony Wolff
Tony Wolff

Reputation: 742

Scheduling an import into oracle from sql server using an odbc connection

I want to regularly update an oracle table from data in a sql server table. I have a odbc connection that allows a manual import of the data using TOAD. I would like to automate this process, any suggestions?

Upvotes: 1

Views: 356

Answers (1)

EdStevens
EdStevens

Reputation: 3872

If you want to push the date from MSSQL into Oracle, then create a 'shared server' in the MSSQL database. That will allow you to simply INSERT the data into the oracle table as if it were local to the MSSQL. Then schedule it with whatever scheduling capabilities are built in to MSSQL. I'm not an MSSQL guy so don't know the details, but it shouldn't be hard to find now that you know what you are looking for.

If you want to pull the data from the Oracle side, then you install the Oracle transparent gateway for ODBC, set up a database link in the Oracle database, create a procedure that does SELECT .... FROM MYTABLE@MSSQL, and schedule it with dbms_scheduler. Being an oracle guy, this is my preferred method.

FWIW, I once had a situation where a duh-veloper was trying to use SSIS to duplicate data from MSSQL to Oracle. It was taking over 5 hours to copy slightly less than 1 million rows. I did exactly what I described above, with the db link in oracle, and was able to copy the data in about 4 minutes.

Upvotes: 1

Related Questions