Indexed or Materialized view replicating on SQL Server DB from Oracle DB

466 Views Asked by At

Goal is for the most simple/nimble way to have a number of tables in a production Oracle DB, replicated on a SQL Server DB (for reporting purposes) and as close to real time.

Currently, I'm doing something like:

insert into SQL_Svr_tab select * from openquery(ORA_DB, 'select * from table')

Only, there's a where clause in the query on the ORA_DB as needed... ruining as job in SQL Server Agent.

My thought/hope is to use either a materialized view in Oracle but the actual object should reside on SQL Server. Thought this post and some other preferences stir me to have it to rather have Indexed View on SQL Server side that would pull from Oracle.

0

There are 0 best solutions below