Q

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
   UNION
   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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close