topic Re: How to split 1 table on 2 table with foreign key ? in Talend Studio

How to split 1 table on 2 table with foreign key ?

_AnonymousUser — Tue, 08 Mar 2016 12:19:49 GMT

Hi !
I have a database where i have for example a table People with different column :
- id
- name
- age
- title

I picked up the column title to have a table Title (like Mister, Miss, ...) with different title in table People (DISTINCT sql).
However i would like to have a link between this new table (Title) and the table People (with a foreign key) but i don't know what to use.
So, if Lucas is a people with Mister as title, i would like his new column (so my foreign key) will be the id of the value mister in the table Title like :
before
TABLE PEOPLE
ID   NAME   AGE   TITLE
1    Lucas   15     Mister
2    Marthe 28     Miss
3    Peter    48     Mister
4    Hugo    23 Mister

and have after :

TABLE PEOPLE    TABLE TITLE
ID   NAME   AGE   FK_TITLE                          ID   NAME
1    Lucas   15     1                                     1    Mister
2    Marthe 28     2                                      2   Miss
3    Peter   48     1
4    Hugo   23     1

Which component can i use to do that ? I thought tMap was the component i needed but i didn't how to do that with tMap...
Can you help me ? 😞

PS : I Hope you understood me, my english is not very good

Best regards,
Wazzouille

Re: How to split 1 table on 2 table with foreign key ?

Anonymous — Wed, 09 Mar 2016 02:26:21 GMT

From your post, @source is people table, @target are people_1 and title table (two table existed on @target) .
This is a simple way , test on PostgresPlus :
1/ insert into title table by using tUniqRow on "title" column of people table
2/ insert into people_1 table by using tMap + look up title table

Tutorial:
+ https://www.talendforge.org/tutorials/menu.php
+ http://www.talendbyexample.com/talend-tmap-component-reference.html
+ http://www.talendbyexample.com/talend-tmap-component-joins.html

Hope this help .

Re: How to split 1 table on 2 table with foreign key ?

_AnonymousUser — Wed, 09 Mar 2016 07:04:01 GMT

Hi !

Thanks for reply, i will look at this today !
I'll give you news !

Wazzouille

Re: How to split 1 table on 2 table with foreign key ?

_AnonymousUser — Wed, 09 Mar 2016 07:35:54 GMT

Re !

I did it succesful for the first (fill title) but i didn't know how you do to make the second tMap, how do you look if the tittle is miss or mister and when you know how you do : is Mister ? fk = 1, is Miss ? fk = 2 automatically

Pending a response from you,
Wazzouille

Re: How to split 1 table on 2 table with foreign key ?

_AnonymousUser — Wed, 09 Mar 2016 07:48:55 GMT

Hi !

I think it works finally, i just put a Left Outer Join and i think it worked !

Thanks for help, was very easy like à thought but i wasn't sure...

Thanks again !

Wazzouille