compare data between table from 2 DBMS

Anonymous — Sat, 16 Nov 2024 12:25:38 GMT

Can someone please advise the best job flow.
I want to compare the result of 2 queries from a different databases (one is SQL server the other is Oracle), there is 20million rows returned from each query.

I am thinking of something like this as it would merge the results of both queries and remove the duplicates but I don't know what components in Talend to use to achieve this (if there is better performance from using a CRC row - pls incorporate that in the job flow):
select cust_id, cust_name, address from sqlserv_table
UNION
select c_id, customer_name, cust_address from oracle_table
Note: there is no primary_key on the tables so (i guess all columns together/or the CRC could be used to form the PK)
Finally I would like to be able to output which rows are present in the sqlserv_table but missing from the oracle_table and vice_versa (to 2 separate files).

Thank you!

Re: compare data between table from 2 DBMS

Anonymous — Thu, 26 Jan 2012 13:35:56 GMT

Hi,
You can use the tMap component. It allows you to make a join between 2 tables according to differents columns, this way you are able to catch which lines are not in the other table. . In my example sqlserv_table is the entry, oracle_table is the lookup. I'm catching the rows which are in the sqlserv_table but NOT in the oracle_table. Just change the table if you want to the inverse (find the line which are in oracle and not in sqlserve). Tell me if you got through some problems.

topic compare data between table from 2 DBMS in Talend Studio

compare data between table from 2 DBMS

Re: compare data between table from 2 DBMS