I have two db with different structures and I want to move data from one to another. Is there a tool that helps to create a migration script to move data from one db to another with different structure? I don’t mean Doctrine Migration tool that is actually an updater/downgrader db structure and is not a data mover.
Please advice.
That would be best to build yourself unless migrating data between well known platforms which migration tools *might exist. Otherwise there is no way for a machine to know how one database maps to another that is completely unrelated. At least not reliably without making a bunch of untrue assumptions.
Ok, I create the tool/script myself but:
I have two databases with different structures and I want to create a migration script to move data from one database to another. How is the best way?
Read ‘Users’ table rows one by one and move to target db? or read all rows at once and assign it to an array then insert that array to target db? then after finishing with users go to orders/invoices/etc?
Then how about moving ‘Orders’ and ‘Invoices’ tables as their user_id may become different if we have already some users in ‘Users’ table and newly moved users may have a different id after being moved? so, better to read User row with its associative orders/invoices/etc. and assign everything to an associative array then move? so assigning all users with its associative invoices/orders in a very big associative array then move, will not crash db if there several thousands rows? or better to read users one by one but with their associative orders/invoices to an array then move?
please advice the best way to create a migration script with two db with different structures?
Have you laid out in detail how the old database will map to the new one?
Yes. I figured out how it maps. The problem is what I described in previous answer. Please advice.
any advice?
Did you build this yourself? Or are you porting across a shop from megento or something? Are there any triggers/crons or special functionality that has to be ported across as well as PK/FK + table structure.
You would best off taking a small sample of data from your DB and making sure you can get that to work by reserve engineering your migration script from that.
Depends on the systems you’re moving between.
Maximum query length (probably the primary issue), PHP memory limit, i would think are your primary concerns.
Optimally, you’d like to execute the fewest number of queries on both the databases.
Thanks StarLion, then what about dependencies? Should I fetch one user wil all its associative orders/invoices/etc.in just one associative array then move it to target db, then go to next user?
I prefer off-loading as much work as possible to queries myself. That isn’t always possible but experience has shown me that using queries to manipulate multiple migrations at once is more concrete than row by row using the application language. It all depends on how complex the data model is though.
So if I move users one by one first, then go to move orders one by one, then move to invoices…
In this case what about foreign key dependencies? imagine in target db we have already 3 users, so the first user of source db will have the id as 4 in target db. so how to move its orders as in source db, its users_id is 1 but should be 4 in target?
The dependencies get migrated first.
Sorry, please give example and explain more in details. I did not understand.
When table A references table B data in table B will be migrated followed by A.
Do you mean first move a user, when moved then query if it has an order then move it, then query if it has an invoice then move it, when all associative data moved this way, then go to next user to move?
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.