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
2 REPLIES
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