Copy a number of views from a linked Oracle database to create tables in SQL Server

Question

I have an Oracle linked server in SQL Server and would like to copy the contents of a number of views to a database in SQL Server, these views from Oracle are to become tables in SQL Server. I have done this one at a time but am looking for a solution to be able to refresh these views, 104 of them, overnight every night.

I am fine with setting the job running manually but am looking for a solution that will either drop and recreate the tables from the views or that will just refresh the data in the SQL Server tables that exist.

Hope I have explained this well enough!

Many thanks in advance for any help on this one.

Answer 1

If you don't already have the table structures in the MSSQL database, I'd say go through one time for all 104 views, and say the following:

SELECT *
INTO MSSQLNewTable (this will be the name of your new table)
FROM <However you reference your Oracle view from within MS SQL Server>

After you do that, then create a SQL Script that says:

TRUNCATE TABLE MSSQLTable_Name
INSERT INTO MSSQLTable_Name
SELECT * FROM OracleTable_Name

.....for each table. Create a job in the database instance that runs on a schedule you set.

use the sys. tables to generate the statements so you don't have to type everything 104 times.