Four Stars

Insert/Update row by row in multiple My SQL tables

I have the following problem:

 

I have 2 mysql databases,

Database1
Tables:
1.status_Table
2.product
3.product_attributes
4.manufacturer
5.prod_category
6.distributor


Database2
Tables:
1.product
2.product_attributes
3.manufacturer
4.prod_category
5.distributor

 

There are some ids in status_Table. All other tables in Database1 contains records against each id in status_Table.
I need to Insert/Update row by row in each mysql table in Database2 from corresponding table in Database1 against each id .
That is,
Database1.product ------> Database2.product
Database1.product_attributes ------> Database2.product_attributes
Database1.manufacturer ------> Database2.manufacturer
Database1.prod_category ------> Database2.prod_category
Database1.distributor ------> Database2.distributor

And at the end of every single row i want to change the status of each id in Database1.status_Table.
How can I do this?

 

5 REPLIES
Twelve Stars

Re: Insert/Update row by row in multiple My SQL tables

depend from how many rows possible different "best ways"

 

as variant:

 

  • read status_Table
  • iterate over all id's - store id to variable,
  • fetch one record from table (use id in WHERE conditions) - do this for all tables in Your list
  • INSERT/UPDATE in tMySQLOutput component
  • update status_Table for selected ID for example tMySQLRow 

 

-----------
Four Stars

Re: Insert/Update row by row in multiple My SQL tables

What are the components used for this. Can u please explain this workflow with talend components using any example?? 

Twelve Stars

Re: Insert/Update row by row in multiple My SQL tables

sample flow:

 

Screen Shot 2018-06-06 at 22.41.36.png

how work each component - please check Talend docs, it pretty good

-----------
Four Stars

Re: Insert/Update row by row in multiple My SQL tables

Screenshot from 2018-06-06 16-41-20.png

The tDB components need external jar files. So is there any solution using other components??

Twelve Stars

Re: Insert/Update row by row in multiple My SQL tables

Smiley Happy

 

between DBInput and DB2Input little difference (like between Mazda and Hammer) Smiley Happy
https://en.wikipedia.org/wiki/IBM_Db2


You need tMySQL* (Input/Output/Row)

it just in Talend Studio 7 they make it universal DBInput with additional settings

 

Screen Shot 2018-06-06 at 23.37.11.png

-----------