I have to migrate data from one database to another and decided to use Talend Open Studio for the task. I've been reading and researching for the last month but only get erratic answers that don't quite address my issue or bring some problems that don't really have anything to do with what I'm asking, which is about doing a small chunk of a migration process properly.
Among others, I've read the following threads, which might give you some insight on how to perform the utmost basic task... which would be moving data from one table to another:
Quickstart database migration using Talend
SQL Server 2008 R2 to MySQL Migration
Just in case you were wondering, the docs are broken and some examples no longer work (such as reusable jobs), so I'm trying to figure out how to do this right, but the research process is overly complicated (or at least that's how it feels to me).
So far I think the idea would be creating 1 job per table + required lookups and orchestrate each job so tables are cascade-dumped. For this example let's pretend we go as follows:
- Standard SQL Server database.
- Standard MySQL Database.
- Both databases have the same tables: Same names and fields.
- 4 tables: Foo, Bar, FooBar and FooLookup. FooBar is a middle table that joins Foo and Bar, FooLookup joins with Foo and stores additional information. It would translate as follows:
[FooLookup] --(1-N)--> [Foo] <--(N-M)--> [FooBar] --(1-N)--> [Bar]
For this simple example, I want to be able to migrate all 4 tables from SQL Server to MySQL the most efficient way possible, which would mean I may want to remove the migration for FooLookup tomorrow, and I'd rather reuse the same connection for each job.
If I could manage to do this properly, I would be able to start thinking about managing constraints and more complex stuff that I'm currently leaving out of the problem. Any tips?
