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

One Star

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

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

Four Stars

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

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 .
One Star

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

Hi !

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

Wazzouille
One Star

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

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

One Star

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

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

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

Download