topic Re: Dynamic Table names from SQL Server in Talend Studio

Dynamic Table names from SQL Server

Anonymous — Sat, 16 Nov 2024 04:51:16 GMT

Hi,

I am pretty new to Talend, I am using Talend Open Studio.

We are trying to dump data from SQL server to S3

We have some table names in SQL server like sales_201906 , sales_201907 , sales_201908 ...

and there is a query which uses this table for example for the current month (August)

Select A.col, B.col

FROM sales_201908 as A Inner join product as B

on A.id=B.id

using tb_input component we have used this query but the sales table should change dynamically based on the month and year, for example for next month query should be as below.

Select A.col, B.col

FROM sales_201909 as A Inner join product as B

on A.id=B.id

How can I achieve this.?

Re: Dynamic Table names from SQL Server

fdenis — Tue, 27 Aug 2019 12:43:16 GMT

first set a context value (who is ilitialized when you run the job).
on sql code :
type "Select A.col, B.col FROM sales_" + context.yourData + " as A Inner join product as B on A.id=B.id"
then create a new job running the first one with trujob.

thats fine

Re: Dynamic Table names from SQL Server

rmartin2 — Tue, 27 Aug 2019 14:49:12 GMT

Hi !

The best way for you is to use a "DBTableList" component. Then iterate on the list of table to do your transfer.

Like :

DBTableList==>DBInput==>tMap=>DBOutput

The table name is a matching condition (a regex), you can use the name of the table wherever you want in the iteration process.

Hope it helps !

EDIT : you can even parallelize the iteration process