How to Use teo different database connection in tdbrow?
I want to load data from one (source) database to another (target)database using insert query in tdbrow. But I can select only one db connection in tdbrow. How to achieve this? It's giving the error table does not exist because I have selected the target db connection so it's failed to get data from source DB.
We have set of tables to be loaded from source to target databse. It's not a simple data migration but the source and target database tables should be in sync on daily basis. So for some table we have to do insert or update as well.
We have defined a dynamic schema in the tdbinput and fetched the select query dynamically for the source db and inserted the data to target db using tdboutput dynamic schema.
Issue comes when we have to insert or update the data in tdboutput. It's required key columns for this operation. But due do dynamic schema we can not define key column dynamically. Also key column is different for different table.
How to set the key columns dynamically in tdboutput? .
We have tried with tdbrow and fetched the merge query dynamically to do this insert or update. But in tdbrow we can select one db at a time so it's not getting tables from source DB.
It is not possible to select two DB connections at one time I'm afraid.
We are supposing you've checked talend CDC feature already as it captures only the changed source data and to move it from a source to a target system(s)
There is no such automatic "Sync" process between two different Databases.
You have to compare tables to get the updated data then insert it to another DB by manual.
For example:
t<DB>input(targetDB)-->main-->tMap-->t<DB>output
t<DB>input(sourceDB)-->lookup-->
Make a inner join and set "Catch Look up inner join reject " as true and the processed data should be updated data, then insert it into another to keep the two database "Sync".
