Urgent:: Loading Oracle Order data into Sql Server DB daily
Posted on 2001-08-03
Our main database is in sql server 2000.
We have to interface with another database (Oracle v ??) to pull orders
I have the sql query to pull orders from Oracle.
I could use ASP / COM to load them into a ADO recordset.
I want to load them into a temp table on SQL Server and then create a stored procedure or some ASP code to re-insert the data into a different Order tables in sql server.
Scheduled Task at midnight: I need to do this on a nightly basis. So I need to schedule a JOB on the w2k server to do this.
I would like to design this so that I have a minimin of changes should the source oracle database or target sql server database change also.
1. Anyone know of a quick way to populate a temp table in SQL Server from an
external Database. ?
2. Advice on whether to use SQL stored procedures or ASP script / COM to access the
data from Oracle and then insert the data into SQL server.
3. Use of SQL Server Utility DTS, I could specify a source as the Oracle database with a sql query to extract the data and copy to the sql server database (work table ). But I want to be able to programmatically trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the real Order entry tables.
W2K Server SQL SERVER 2000, IIS 5.0 ASP / VB / COM+