Compare millions of records from Oracle to SQL server

I would create an SSIS package and load the data from the Oracle table use a Data Flow / OLE DB Data Source. If you have SQL Enterprise, the Attunity Connectors are a bit faster. \n\nThen I would load key from the SQL Server table into a Lookup transformation, where I would match the 2 sources on the key, and direct unmatched rows into a separate output.\n\nFinally I would direct the unmatched rows output to a OLE DB Command, to update the SQL Server table.\n\nThis SSIS package will require a lot of memory, but as the matching is done in memory with minimal IO, it will probably outperform other solutions for speed. It will need enough free memory to cache all the keys from the SQL Server Table.\n\nSSIS also has the advantage that it has lots of other transformation functions available if you need them later.",

What you basically want to do is replication from Oracle to SQL Server.\n\nYou could do this in SSIS, A windows Service or indeed a multitude of platforms. \nThe real trick is using the correct design pattern.\n\nThere are two general design patterns \n\n\nSnapshot Replication\n\n\nYou take all records from both systems and compare them somewhere (so far we have suggestions to compare in SSIS or compare on Oracle but not yet a suggestion to compare on SQL Server, although this is valid)\n\nYou are comparing 18 million records here so this is a lot of work\n\n\nDifferential replication\n\n\nYou record the changes in the publisher (i.e. Oracle) since the last replication then you apply those changes to the subscriber (i.e. SQL Server)\n\nYou can do this manually by implementing triggers and log tables on the Oracle side, then use a regular ETL process (SSIS, command line tools, text files, whatever), probably scheduled in SQL Agent to apply these to the SQL Server.\n\nOr you could do this by using the out of the box replication capability to set up Oracle as a publisher and SQL as a subscriber: https://msdn.microsoft.com/en-us/library/ms151149(v=sql.105).aspx\n\nYou're going to have to try a few of these and see what works for you.\n\nGiven this objective:\n\n\n \n I want to consume less resource to achieve this functionality which takes less time and less resource\n \n\n\ntransactional replication is far more efficient but complicated. For maintenance purposes, which platforms (.Net, SSIS, Python etc.) are you most comfortable with?",

Other alternatives:\n\nIf you can use Oracle gateway for SQL Server then you do not need to transfer data and can make the query directly.\n\nIf you can't use Oracle gateway, you can use Pentaho data integration or another ETL tool to compare tables and get results. Is easy to use.",

I think the best approach is using oracle gateway.Just follow the steps. I have similar type of experience.\n\n\nInstall and Configure Oracle Database Gateway for SQL Server.\nhttps://docs.oracle.com/cd/B28359_01/gateways.111/b31042/installsql.htm\nNow you can create a dblink from oracle to sql server.\nCreate a procedure which compare the missing records in oracle database and insert into sql server database.\n\n\nFor example, you can use this statement inside your procedure.\n\n INSERT INTO \"dbo\".\"sql_server_table\"@dblink_name(\"column1\",\"column2\"....\"column5\")\nVALUES\n(\n select column1,column2....column5 from oracle_table\n minus\n select \"column1\",\"column2\"....\"column5\" from \"dbo\".\"sql_server_table\"@dblink_name\n)\n\n\n\nCreate a scheduler which execute the procedure daily.\n\n\nWhen both databases are online, missing records will be inserted to sql server. Otherwise the scheduler fail or you can execute the procedure manually.\nIt takes minimum resource.",