I’m fully redesigning a site that indexes a number of articles with basic search functionality. The previous site was written about a decade ago, and I’m salvaging about 30,000 entries with data stored in less-than-ideal formats. While I’m moving from MSSQL to MySQL, I don’t need to make any “live” changes, so this is not a production-level migration issue so much as a redesign.
For instance, dates are stored the same as tags/subjects about the articles, but in strings as “YYYYMMDDd” (the lowercase d stands for “date” in the string). Essentially, before or after I move from the previous database format to a new one, I’m going to need to do a lot of replacement of individual entries. While I understand how to do operations with regular expressions in non-database issues, my database experience isn’t robust enough to know the best way to handle this.
What is the best (or standard) way to handle major changes like this? Is there an SQL operation I should be looking into? Please let me know if the problem isn’t clear–I’m not entirely sure what kind of answer I’m looking for.
4
Have you considered writing some Java/C#/other language of your choice to do this? If this is just a one off Extraxt, Transform, Load process then you could just cobble together some code that reads rows from the original database, performs some transformation (eg: parsing the date string and converting it into a date object) before saving it to the new database. You can make this code as nice as you need/want: feel free to create POCO/DTO classes for both the old and new database designs and use an ORM tool like (N)Hibernate to handle the mappings. You can have a DateParser interface/base class that is appropriately implemented/extended. And so on.
While I appreciate that I haven’t given you a concrete “Do it this way to solve your problem” answer, I hope the above information will get you moving in the right direction. For what it’s worth, I recently worked in a team that specialised in data migration between a variety of systems and all this information comes from my experiences there. I am more than happy to expand on anything if you want more information, please just ask in the comments.
One way which I can see to handle this case is (though it might not be ideal way):
- Write MS SQl query to fetch data in form which can be imported into MySql db like changing date from string to proper date
- Store above result in file
- Write MySql query to directly store data from file to database