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