In a very simple case, like when there is only data from one table to be migrated, you can put all your records manually, if the number of records are quite large, you can run an Insert Query.
However, this does not work with databases that have relationship based data.
So the question is,
What do you do when the relationship between two or more tables have to be maintained?
In this case, manual entry can be very tiresome, and is very prone to errors. I would say its probably not even humane to even try and make someone do this. Manually eyeballing and entering the records is very time consuming. To make matters worse, if there are any mistakes, then making corrections and repeating the same process will reduce your efficiency to the lowest.
Using script for merging
Saving your data using a script is more feasible, less time consuming, and more importantly a safer choice. Scripts are a set of instructions you define to carry out your task, and can be run without user interaction. There are many scripting languages, such as Perl, Bash, PHP, JavaScript, etc. in the context of this article, we are using PHP.
Passing data to script using csv
There can be a number of ways to feed data to your script, and one of them is through a .csv file.
To begin, start by saving your data from your old database into a .csv file (for which, again, you can write a script), be sure to provide proper headings for each column.
Then follow the following steps:
Provide path of your .csv file
$csvFilePath = /path/to/your/file
Open the file
$handle = fopen($csvFilePath, "r");
Read the 1st row as headings
$header = fgetcsv($handle);
Read each row with data in the file
while (($row = fgetcsv($handle)) !== FALSE) {
$row = array();
// for each header field
foreach ($header as $key => $head) {
$row[$head] = (isset($row[$key])) ? $row[$k] : '';
}
//write here the code to save your data
}
Here each time the while loop runs, data is being read from a new row in the file and is converted into an associative array ($row) with headers as the key and data as the value. Add your code to save data into your database right after the foreach loop.
Execute the script
After you are finished writing your scripts, its time to run them on test server. The script might crash due to some error, but do not panic. Using scripts allows you to debug the issue very quickly and easily. You might also find some data that you missed out, which were important in maintaining the relations between tables. Fix those mistakes and try running the script again until the whole migration passes without error.
Test the result
A simple test you can do to confirm the success of a migration is to count the number of rows in each db, and check the sum after migration to see if it match. Also perform unit tests and regression tests to check the new data is completely compatible with the new database architecture and the system as a whole, making sure that it has not caused some previously working code to crash.
Hard work done
If everything went fine on your test server, then you should not face any issue on production server as well. Just take care of your tasks, one by one, and soon enough you’ll have your databases merged. But remember, take your backups and still be careful, because
Err is human 🙂
