title: Compare millions of records from Oracle to SQL server
body: <p>I have an Oracle database and a SQL Server database. There is one table say <code>Inventory</code> which contains millions of rows in both database tables and it keeps growing.</p>
  
  <p>I want to compare the Oracle table data with the SQL Server data to find out which records are missing in the SQL Server table on daily basis.</p>
  
  <p>Which is best approach for this?</p>
  
  <ol>
  <li>Create SSIS package.</li>
  <li>Create Windows service.</li>
  </ol>
  
  <p>I want to consume less resource to achieve this functionality which takes less time and less resource.</p>
  
  <p>Eg : 18 millions records in oracle and 16/17 millions in SQL Server</p>
  
  <p>This situation of two different database arise because two different application online and offline </p>
  
  <p><strong>EDIT</strong> : How about connecting SQL server from oracle through Oracle Gateway to SQL server to </p>
  
  <p>1) Direct query to SQL server from Oracle to update missing record in SQL server for 1st time.</p>
  
  <p>2) Create a trigger on Oracle which gets executed when record is deleted from Oracle and it insert deleted record in new oracle table.</p>
  
  <p>3) Create SSIS package to map newly created oracle table with SQL server to update SQL server record.This way only few records have to process daily through SSIS.</p>
  
  <p>What do you think of this approach ?</p>