Get Multiple MySql Database Tables and replicate to MsSQL

[Tim_D_Baker]

I have a remote MySQL Database with 147 tables. I need to get all the data from remote server and replicate in local MsSQL.

So far I've used tDBIput to get table from database and sent tDBOutput. This works fine - EXCEPT I can only do one table at a time.

So far I've duplicated the above process 13 times for the first 13 Tables in the one Job.

There must be a better way - even if it involves getting a subscription.

I'm new to Talend - but have searched extensively for answer without result. Its probable I'm not asking the right question.

Could someone point me in the right direction.

[vikramk]

Hi @Not defined Not defined​ ,

You could implement something like below.

tdbconnection-->tdbinput-->tdboutput-->onsubjoberror->tdbrollback

• In 'tdbinput' you could make use of query similar like this- 'select * from information_schema.tables'.
• At 'tdboutput' you could use dynamic schema option if you are user of talend studio. we don't have this option in talend open studio.

Please let me know if it helps.

[JohnRMK]

Hello,

you can have two approaches. One free and one paid

The first requires skills in the dba and system domain

The approach: Have shell scripts that will connect to your source database and retrieve all the data in csv or sql files. You will need to know the name of your tables and fields

https://dataedo.com/kb/query/mysql/list-table-columns-in-database

You can get help from Talend with the tSystem component to run your shells and organize your directories properly

PS: Talend will not read and insert data, you will do it with system scripts

THE second approach is to take a paid version of Talend. Talend offers the dynamic schema which allows you to read hundreds of thousands of tables with a single job and perform the insert.

If you are looking for dynamic schema and mysql in the forum, you will find two examples with the jobs I wrote a few months ago that answer your problem

It's up to you to decide, if it's long term or short term to buy a license

[Tim_D_Baker]

Thanks for the response guys - I will look into your suggestions