Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: