How to automatically store data from Oracle in SQL Server (according to a schedule)

Question

Hello, I'm new here, so sorry, if my question is too basic. However, maybe you have some advice, example, links, which could help me... I'm trying to find something helpfull for few days, but no results as for now.

I'm working in a distributed environment. I have a Oracle server hundreds of miles away and a MS SQL server close to me. I'm writing a application using Visual Web Developer 2008 Express. I need some data from Oracle. It's not worth to query the Oracle server every time i need some data from it. I'd prefer to run some Oracle queries once each night and store results in some local (SQL Server) tables. I assume, I should run queries through standard windows scheduler (Windows Server 2008). I have the basic connectivity - I can open Oracle Database from local Visual Studio.

The questions are:

1. How to write a query/procedure/function that would get data from Oracle and put them into a SQL Server table (possibly recreated before each query run)?
2. How can I run such a query from command line (or in other way run from scheduler)
3. What naming conventions are applicable? In VS I use something like //IP.IP.IP.IP/Name and a user with password.

Thanks for any help or advice.

Regards, Matteo

Answer 1

I suggest you speak to the DBA's of the Oracle and SQL Server databases, as there may be other considerations you need to bear in mind. (Data Integrity, Security, ownership etc.)

One route you could follow would be to implement DTS (For older databases) or SSIS (for new versions of SQL Server) processes to copy the data across on the schedule you want. (This is pretty much what they were built for.)

Answer 2

Thanks...

I'm the DBA for the SQL Server, which will serve only for my application. For Oracle I just want to read data and I have enough privileges and agreement with DBA's. Security, ownership and integrity are not an issue for now. I just need some technical advise how to get data from Oracle to MSSQL tables on a schedule.

I use MS SQL Server 2008 Express SP1. I'm very close to solve my problem - I have established connections and everything installed and working. I just don't know, how to run a query, which would get data from Oracle and put into MSSQL, on regular basis, without manual interaction.

I've some experience in programming, but not much in databases (except creating complex SQl queries). Therefore some example or links to detailed description would be helpful. I'm not sure about naming conventions, differences between procedures, funtions and queries, command line options to run db automation procedures and so on. I'm also not sure, about which mechanisms or technologies are available in MS SQL Server 2008 Express edition.

Answer 3

How much data are we talking about? If there is a small quantity that you need to transfer every day, you can write a stupid fetch and insert script in language of your choice. You only need to search for better solutions if "sync" would take too much resources.