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.