3

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.

  • According to Joel (Jo for friends), you should first migrate the application "as is" and only once it is stable begin making changes to data types: joelonsoftware.com/articles/fog0000000069.html – SJuan76 Oct 18 '13 at 18:53
  • That's not really an option. It was a one-off, non-supported site that hasn't been touched in years. It was designed and written poorly, and I'm not really "losing" anything by rewriting it (I didn't write it the first time, which I think is part of what the article addresses). Thank you for the link, though--it'll definitely come in handy for other projects. – ramblinjan Oct 18 '13 at 18:55
  • 1
    Date convert in SQL Server: stackoverflow.com/questions/1509977/… – Jon Raynor Oct 18 '13 at 20:50
  • Just get it in a format the MySQL can handle. YOu can keep it as a string (varchar) and drop the "d" or convert it to an actual datetime datatype in SQL Server. – Jon Raynor Oct 18 '13 at 20:52
1

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.

0

One way which I can see to handle this case is (though it might not be ideal way):

  1. Write MS SQl query to fetch data in form which can be imported into MySql db like changing date from string to proper date
  2. Store above result in file
  3. Write MySql query to directly store data from file to database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.