One Star

Dynamic Schema to save different column names in database tables

Hi,
In my mysql database I have 4 tables.
1. inv_ppl1_flat -- (id, style, color, code, size)
2. inv_ppl2_flat -- (id, style, color, code, size)
3. inv_ppl1_lookup -- (id, ppl1_style, ppl1_color, ppl1_code, ppl1_size)
4. inv_ppl2_lookup -- (id, ppl1_style, ppl2_color, ppl2_code, ppl2_size)
In TOS, I created context variables with name "people" and values to "ppl1" and "ppl2"
I want to get data from ppl1/ppl2_flat table and insert to ppl1/ppl2_lookup table.
In the "tMySqlInput" I given Table name to "inv_"+context.people+"flat" and in "tMySqlOutput" I given the datatabse configurations. But I want to edit the schema. As you can see the column names in 2 lookup tables are different. the style, color, code and size column is preceded by the appropriate name(context variable value). So I cannot map to correct schema. I want to map
style to ppl1/ppl2_style
color to ppl1/ppl2_color
rest are similar

In the edit schema I cannot add context variables. So How can I perform mapping to different (but follows a naming pattern) column name and extract data from first table and insert to second?
Thanks
5 REPLIES
One Star

Re: Dynamic Schema to save different column names in database tables

Database names have been modified for simplicity. I'm trying to get some dynamic sql in place to update static copies of some key production tables into another
One Star

Re: Dynamic Schema to save different column names in database tables

Where I can update the sql query?
Seven Stars

Re: Dynamic Schema to save different column names in database tables

I think you can do this by using the "Additional columns" option in tMysqlOutput.
For example, if your tMysqlOutput schema is (id, style, color, code, size), set Name to context.people+"_style", SQL expression to "?", Position to "Replace" and Reference column to "style". Add settings for each column.
Otherwise, the only ways to do this using TOS would be:
1. Have a tMap with a separate output for each lookup table with the correct column names, with a filter expression using your context variable to control to which tMysqlOutput the data goes.
2. Use tMysqlRow to manually create the INSERT INTO statements for your lookup tables.
Using the Enterprise version of Talend, you could use a dynamic schema instead.
One Star

Re: Dynamic Schema to save different column names in database tables

In the edit schema I cannot add context.people+"_style". I don't want to map each table separately as I may need to add many more tables soon
Seven Stars

Re: Dynamic Schema to save different column names in database tables

In the edit schema? The instructions I gave were for the "Additional columns" option.