Loading multiple table from source into multiple table into target in Talend

[DM2017]

I have around 25 tables to load to target with same structure & uses same
logic for loading. I have prepared one job which does that, but it's a
long process to design all the table. Is there any way to pass the table
name and load to target, basically a small job(in size).

I am using Talend open studio & mysqlDB.

Any help & suggestion is truly appreciated 

[Anonymous]

Are you talking about 25 tables in the same database which are all of the same structure, but have different table names? Are these tables your sources? Are you loading them all into one table as your target? If so you can do this quite easily. You will need a list of your source table names in a database table or a flat file (your choice) and you will need to read that into a Talend job. Connect a tFlowToIterate to that source component. Then connect an iterate link between the tFlowToIterate component and your source db component. If your list of table names is returned to the tFlowToIterate in a column called "tablename" and the row connecting to the tFlowToIterate is called "row1", then you can reference your table name in your db component's SQL query like below.....

"SELECT
Column1,
Column2,
Column3,
Column4
FROM " + ((String)globalMap.get("row1.tablename")) + "
WHERE......"

For every iteration of the tFlowToIterate, you will retrieve every row from the tablename provided to that query.

Hope that helps

[DM2017]

Thanks for the reply, I asked for something like below.

server 1 db1 table1 >>>> server 2 db1 table1

server 1 db1 table2 >>>> server 2 db1 table2

.......

server 1 db1 table25 >>>> server 2 db1 table25

Basically, both table in the DB for both server as same structure, just doing a tmap and sending to target.

I have just attached the image for example purpose, like this I have 25 tables. 

Capture.JPG

[Anonymous]

The way I suggested will still work. You will just have to use the globalMap variable I used in the SQL query as the "Table Name" parameter in your database output table.

[DM2017]

Would you mind to just provide the layout of the job design.

Thanks a lot

[Anonymous]

A rough layout would look like below.....

You can replace the tFileInputDelimited_1 with a database component holding your list of table names if you want. That isn't important. It is the tFlowToIterate and the globalMap variable usage in the db components that is key.

[Anonymous]

Please can you help in step by step method, i am using global variable here but not getting the data from database. 

I am iterating the list of tables and trying to push data into mysql DB 

Thanks in advance!

[Anonymous]

Can we use this for tables having different schema from MSSQL to MYSQL

Please let me know 

Thanks 

[Anonymous]

There is a limit as to what can be done with a single job in Talend. You cannot infer schema changes within the same job unless you are using the subscription version of the product. This gives you access to the Dynamic Schema. I talk about a method of using that here:

https://www.talend.com/blog/2019/11/11/migrate-data-between-databases-one-job-using-dynamic-schema/

[Anonymous]

Hi, thanks for the idea. I've been trying to implement it, but I still find difficulty in configuring my DBInput component. I'm using an MSSql Database. Kindly help with the configuration. Thanks, in advance.