I have the same information stored both in Teradata and Oracle databases. I need to compare this information row-by-row. Another one issue is that this data is stored using different data types. How could I do it?
Printable View
I have the same information stored both in Teradata and Oracle databases. I need to compare this information row-by-row. Another one issue is that this data is stored using different data types. How could I do it?
You have at least three options:
1. You can try to find some third-party database comparison tool. I don't know, whether they can successfully compare different databases, but you may try. I have some concerns here, especially for ability working with Teradata. We've used it for several projects, and it has not much community and is not as opened for a user as MSSQL, for example. Although, tool can work with SQL queries so it could use any database. Anyway, if this option fails, then...
2. You need to create another one database and put all information from Teradata and Oracle here first. At this process you had to deal with data types casting and apply some untrivial transformation rules, but at the end you should get tables in the single coordinate system (I mean, a single database with the tables having columns with the same datatypes). At this step you can do any SQL comparison as you need. You haven't provided details about your tables count and data amount. But if the tables are rather compact, I would export data from both Teradata and Oracle to the excel(or csv) files and do the comparison between files 3. Ask specialists who can assist you in this question. You can ask these guys https://www.issart.com/en/. I sometimes use their help.
You could try HVR from https://www.hvr-software.com/. It is a change data capture/replication tool, but it also will compare databases row-by-row across different DBMS. They support Teradata, MySQL, MS-SQL, Oracle, PostgeSQL, and several others, as well as text files. I think you can get a 30 day free trial.
Thanks!