topic ETL that reads select query from inside a column and inserts the resulted rows into another table in Talend Studio

ETL that reads select query from inside a column and inserts the resulted rows into another table

APelmus — Tue, 14 Feb 2023 15:36:10 GMT

Hello everybody,

I am a beginner with Talend and this might be very easy to do, but for me seems a little bit complicated, so I would appreciate your help.
I have a table (TABLE A) that contains a column in which different queries are stored. For every row from that table, the column named 'query' contains a select statement.
Each of these queries selects same kind of columns (so the table resulted for each of the query looks similar (same number of columns and same type of data)
I need to read the data resulted from running the select statement from within the column 'query' of the TABLE A and insert them into TABLE B.
Table B's columns are the same as the columns from the select statements.
I think i need to select the query column from the TABLE A and then create a context for that column and next to somehow read the data using the context and insert it into TABLE B but i don't know what kind of components I should use.

Thank you!

Andreea

Re: ETL that reads select query from inside a column and inserts the resulted rows into another table

BenG — Wed, 15 Feb 2023 16:38:13 GMT

Hi Andreea,

I hope I understood it correctly, I just made my own example.

I have created a table "Queries" that contains in one column the queries

Then I have a table "Table A" with the values I query.

The Job design looks like this

I load the table with the queries, the tMap_2 is just assign the field to the next component (if you would have more fields in the query table) after that its going into a tFlowToIterate and then to the table which contains the data you want to query. The rest is easy just another tMap to Map the fields and then a tUnite to unite the data into one stream into TableB.

Here is the important stuff on the query config in TableA (you need to assign the variable from the tFlowToIterate component:

I hope that is what you were looking for.

Cheers

Ben

Re: ETL that reads select query from inside a column and inserts the resulted rows into another table

APelmus — Tue, 21 Feb 2023 11:39:04 GMT

Thank you very much!