asked
Compare millions of records from Oracle to SQL server
I have an Oracle database and a SQL Server database. There is one table say Inventory which contains millions of rows in both database tables and it keeps growing. 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. Which is best approach for this? 1. Create SSIS package. 2. Create Windows service. 3. OR any other better approach I want to consume less resource to achieve this functionality which takes less time and less resource. Eg : 18 millions records in oracle and 16/17 millions in SQL Server
To really make this work, you need to have a way to filter the data down to what's been modified. If you attempt to compare 16 million rows to 16 million rows every day to look for differences, regardless of the method you use, it's going to be extremely painful. Once you have a way to filter the data down to have just the data modified for a given period, then, probably, instead of SSIS to move data in and out of the servers I would probably just use OPENQUERY to run the comparison directly. But I would only do this if I could filter the data. If you can't filter the data and have to do the full 16 million row comparison, this method will be very painful.
19 People are following this question.
