compare and migrate two postgresql tables of two different DB

One Star

compare and migrate two postgresql tables of two different DB

Hi all, 
I'm new in Talend and I want to compare 2 columns in 2 different databases then migrate 2 tables. This is an example of what I aim do:
- In BD1 I have table T1 wich contains many columns like : id | name
                                                                                              1 |  A
                                                                                              2 |  B
And T2 wich contains  only 2 columns the ids of T1 and the ids of another table like this :     id1 | id2
                                                                                                                                                  1   |  11
                                                                                                                                                  2   |  12
- In DB2 I have a table T3 wich is similar to T1  with the same names but with different ids :  id | name
                                                                                                                                                 20 |  A
                                                                                                                                                 21 |  B
and T4 similar to T2 but empty .
1. Frist I have to compare the column name of T1 and T3 (if T1.name = T2.name)  then 
2. migrate T4 wich must be like this : id1 | id2
                                                            20 |  11
                                                            21 |  12
Is there a way in Talend Open Studio  to do that ! Help me please.
Thank you,
Emna
Moderator

Re: compare and migrate two postgresql tables of two different DB

Hi,
If I understand your requirment very well,  you can use TalendHelpCenter:tMap to achieve your goal.
To make it clear, I have designed a demo job. Hope it will be helpful for you.
Please see my screenshots
Best regards
Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: compare and migrate two postgresql tables of two different DB

Hi Sabrina, thank you for help ! 
I tried your example but that wasn't the expected result , there where only 3 rows are migrated, I simplified the problem but actually this is the real situation : 
the table T3('groups_users')  contains the ids of the groups and the users wich is a many2many relation between 2 tables 'groups' and 'users', The table users is the same in the 2 DB this is not the case for the table 'groups' 
in the table groups we have the columns 'id', 'name'  and 'category_id', there are some similar names but for different  'category_id' like this example :
id |   name    | category_id 
1  |  A           |  6
2  |  A           |  224
3  | B            | 56
the column category_id  contains the ids of another table 'model_category' in this table we find many columns too like id, name .. (same names in the 2 DB)
what I aim do is to find the id of the group in the other DB to migrate the table T3('groups_users')   :
we have the same names of the 'model_category' in the 2DB  but with different id same case tof the table 'groups ' so 
if  DB1.model_category.name = DB2.model_category.name then :
        if DB1.group.name= DB2.group.name (for the same category_model) 
             then migrate T3
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PS: T3 doesn't contain a primary key, I defined it
This screenshots contain the test that I did. Please take a look on that and help me to resolve it.
Best regards, 
Amna 
 
 

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads