Comparing data from source column with target column
I want to compare data in different databases. There are five source databases (3 in Oracle, 1 in SQL Server, 1 as flat file) -- target is an Oracle database. I want to compare the data from the source table column with target table column. Could you give an easy way of doing this?
The easiest way to do this is to purchase a piece of third party software that lets you compare data in different databases. If you do not have that available, then you will have to write your own routines. I would suggest moving all of the tables to a common data store. This might mean that you import the SQL Server data and the contents of the flat file to an Oracle database. Once you've imported the data, then a simple query can determine the differences. Let's assume that I have one table from SQL Server and one table from Oracle. Call them sql_table and ora_table. They each have three columns, colA, colB, and colC. If I want to know which rows are in one and not the other, then I can issue a query similar to the following:
SELECT colA,colB,colC FROM (
SELECT colA,colB,colC FROM sql_table
SELECT colA,colB,colC FROM ora_table)
GROUP BY colA,colB,colC HAVING count(*) = 1;
The above query will return the row of information that is in one
table, but not the other.
This was first published in May 2004