Read tables with same structure from different DB and create one joining all the inputs

[FGuijarro]

Hi,

I´m trying to read 5 tables from DB1, DB2, DB3, DB4 and DB5.

All the tables are called with the same name AND all of them have the same structure

The output will be 1 table with all the content of the 5 input tables.

Can anyone help me, please?

Thank you

[Anonymous]

Hi

Are all DB connection information the same except database name? If so, I think you can write each database name on tForEach component and then iterate each database name, the job looks like: (take Mysql database for exxample)

tForEach--iterate--tMysqlInput--main--tUnite---tMysqlOutput

on tMysqlInput, set the database field with global variable:

((String)globalMap.get("tForeach_1_CURRENT_VALUE"))

tUnite: merge all the output from each database.

Regards

Shong

[FGuijarro]

Hi Shong,

Thanks for your answer.

Definetly it worked!!

I just have an issue because it seems there are some repeated records in the 5 table readed from the diferent databases with same primary key. So after run it, I received message

[statistics] connected

(conn=453) Duplicate entry '3510' for key 'PRIMARY'

(conn=453) Duplicate entry '488' for key 'PRIMARY'

(conn=453) Duplicate entry '486' for key 'PRIMARY'

[statistics] disconnected

Query used in tDBInput is:

"SELECT 

 `venta`.`id`, 

 `venta`.`cliente`, 

 `venta`.`fecha_exp`, 

 `venta`.`operacion`, 

 `venta`.`codigo_servicio`

FROM `venta`"

And database field contents: ((String)globalMap.get("tForeach_1_CURRENT_VALUE"))

Is there any option to copy every single row from the 5 tables (one from one different DB) although primary key could be the same in table_1 than in table_2?

Many thanks!

[Anonymous]

I think the error occurs when you trying to insert duplicated entry into target database, if you don't want this error, you can filter the duplicated records before inserting.

tForEach--iterate--tMysqlInput--main--tUnite--tUniqRow-tMysqlOutput

[FGuijarro]

Hi Shong,

Thanks for your answer. I checked records and DB had duplicated records by the end of each month so it would be a great idea to add an ammount to the first id of the next month for not to repeat ids when changing month.

Thanks!