compare tables data

Pacific007 · Post by **Pacific007** » Thu Jan 19, 2012 3:45 am

Hi,

I have pairs of tables metadata is partially matching and I need to compare data of every pair of table most preferably on key column as metadata is partially matching. In output I want the both the tables data count and the mismatch count and a flag column if comparision match or doesnot match.

I want generic Job so that all the pair of tables can be match with out doing any further modification to the job.

Please provide me inputs and revert if anybody needs further details.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 19, 2012 2:59 pm

You need a special component from Unseen University School of Inadvisably Applied Magic.

Pacific007 · Post by **Pacific007** » Fri Jan 20, 2012 8:37 am

Ok I will explain it more easily...The problem is we have source table and target table, we are loading target table with some transformation, but key columns are not derived just matched with may or may not data type conversion. now we have to match the source and target tables pairs and found out the count at source side, target side and a flag column if matched or not matched.

chulett · Post by **chulett** » Fri Jan 20, 2012 9:02 am

So, "compare tables counts" rather than "compare tables data" it would seem. What have you tried? Seems like parameterized table names, selecting a count from both the source and target tables, joining those two inputs and comparing the results would be fairly straight-forward.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 20, 2012 12:40 pm

Don't think so, Craig. The question is counts of matched and non-matched records from a join, but without metadata. This is impossible; even with RCP enabled, at the very least join keys must be specified. There is an approach using upstream Modify stage to generate join key names using job parameters.

chulett · Post by **chulett** » Fri Jan 20, 2012 3:38 pm

OK, I was thinking a mismatch of the total count was all that was needed, but if it is counts of matching and non-matching records per table then as you said it's not possible without some amount of metadata.