Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Interesting Question - Compare 2 tables

Interesting Question - Compare 2 tables

Interesting Question - Compare 2 tables

Hi everyone,

I am using Informatica 7.1.5 with Oracle 8g database.

Ok here is my situation

Our current ETL tool is DataStage and we bought Informatica and we are in the middle of converting all of our existing ETL jobs.

So what I do is, redesign an existing ETL job in Informatica to do EXACTLY the same thing as the original DataStage ETL job.  Thus, as part of my unit testing, I have to make sure that not only the row count in the target table is the same but that every row has exactly the same data in each column, as with the original table.  

In this regard I have to compare two tables that in theory should be identical.

Imagine this:

DataStage ETL job populates target Table A
Informatica mapping/workflow populates target Table B

Table A and Table B SHOULD be identical, if I have done the redesign in Informatica correctly.

If I do this testing in SQL, within the database and check for each column, it is very time consuming.

I was hoping there would be a cool way to do this in PowerCenter Designer.  Perhaps something like

- create a new mapping
- make Table A your Source table
- make Table B a lookup transformation
- drag all the ports from Table A to the lookup transformation, and then check for equality
- then somehow use a router transformation that gives me all the PASS and FAIL.  The failures would be where for a single row, the data in TableA.colX did not match TableB.colX.  However, I want the mapping to tell me for which row, which columns (ports) did not match.

Is this possible?


RE: Interesting Question - Compare 2 tables

Let's assume that you do a very good job and expect very few differences.
One way to perform a simple pre-check is the following:


select * from table A
select * from table B

Identical tables would yield an empty query result.
There is a pretty good chance that you may not have to design the type of mappings that you looking for.

In any case you can limit the effort by performing the pre-check (which is a simple exercise anyway)

Ties Blom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close