Skip to main content
Solved

compare table data cross database


Forum|alt.badge.img

I am very new to use this tool and thought some of expert from this forum might help me

I have oracle11 database and migrated to MS SQL server 2014

I would like to compare data between oracle11->database1->table1 with mssqlserver2014->database1->table1

I would like to compare data between each table with cross databases

Please, let me know steps to do this

Best answer by david_r

There are several ways to accomplish this, but here's the simplest one:

  • Insert an Oracle reader that reads oracle11->database1->table1
  • Insert an MS SQL Server reader that reads mssqlserver2014->database1->table1
  • Send both reader outputs into a ChangeDetector transformer.

View original
Did this help you find an answer to your question?

6 replies

ciarab
Contributor
Forum|alt.badge.img+9
  • Contributor
  • August 11, 2017

I'd probably try the change detector- pretty simple to use and I've always had success with highlighting any differences between 2 tables using this

https://www.safe.com/transformers/change-detector/


david_r
Evangelist
  • Best Answer
  • August 11, 2017

There are several ways to accomplish this, but here's the simplest one:

  • Insert an Oracle reader that reads oracle11->database1->table1
  • Insert an MS SQL Server reader that reads mssqlserver2014->database1->table1
  • Send both reader outputs into a ChangeDetector transformer.


fmelizard
Contributor
Forum|alt.badge.img+17
  • Contributor
  • August 11, 2017

Hi @click4vk, the ChangeDetector, UpdateDetector, or Matcher can be used to compare the tables depending on your comparsion criteria. Check out: Change Detection


Forum|alt.badge.img
  • Author
  • August 15, 2017

Thanks for your response

I am very new to use this tool

I have created one reader for oracle and one more reader for MS SQL server 2014

How to create changedetector and view the difference in the data? If someone can give me a step by step instructions on how to do this, that would be very helpful.


  • February 27, 2018

I would choose the UpdateDetector, as it uses the key attribute to have a key on how to make the comparison.


helmoet
Forum|alt.badge.img+8
  • October 18, 2019

One would like to keep in mind when using the ChangeDetector:

  • Attributes coming from Oracle fieldnames are usually converted to uppercase, whereas MSSQL attributes do not (fix by BulkAttributeRenamer);
  • Oracle attribute encoding might be different from MSSQL encoding (fix by AttributeEncoder)
  • Oracle reader adds different attributes than MSSQL (fix by AttributeRemover, sorry, AttributeManager)
  • Coordinate precision might not be the same across database platforms (be careful using CoordinateRounder)

All of this might cause the ChangeDetector to conclude that there is a change where humans usually find there is not. 

It is also good to remember setting the parameters for database read block size when comparing large database tables, it takes a lot of overhead scraping 2,500,000 records from a database server in chunks of 100 records each:

MSSQL_ADO_IN_READ_CACHE_SIZE_MSSQL_ADO
ORACLE_NONSPATIAL_IN_CHUNK_SIZE_ORACLE_NONSPATIAL

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings