newbieuser (8/21/2012)
Thanks for your replies.. we need to migrate 3-4 tables in Oracle to MSSQL and refresh the data on an ongoing basis.There are about 50000 records in each table and the script should be able to truncate the table first and then refresh the data from Oracle via the existing linked server..
We cannot use a view to point to the Oracle data as they are on different network and every time we query the view, it goes through the linked server and we are trying to avoid that.... Not sure if there is something like materialized views in MSSQl..
For 50K rown in four to five tables I would probably consider doing it the easy way. Just write the T-SQL script on SQL Server side refreshing the tables through the linked server connection then schedule it as a job to automatically execute when appropriate.
As a side note, the closest thing to an Oracle Materialized View offered bh SQL Server is "Indexed View".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.