I am using a LinkedServer in SQL 2012 and refreshing a table from Oracle 9G using below procedure on daily basis. The current records in the table is 15M and it is increasing every day by 2-3K new records and the old records are also deleting and updating randomly. It takes 7-8 hours to complete this job overnight.Considering the table is already optimized on index level at Oracle side, What can be the most efficient way to attempt this? My current process is below :
Truncate table SQLTable
Select * into SQLTable from openquery (LinkedServerName,'Select * from OracleTable')
It doesn't make sense to truncate 15M rows just for 3000-8000 rows changes.
I would consider using an ETL tool like https://sourceforge.net/projects/pentaho/. You can start with a free community edition.
This tool provides a Spoon tool that basically provides graphical interface to create a workflow. With the Pan tool you can execute the file you create using spoon tool. Basically create a batch file with Pan command and provide .ktr file as an argument. Now, this batch file you can schedule using windows task manager or Unix CRON Job.
With this, you can create a workflow, which can look for changes and only insert or update changes.