balmoral — 2011-06-29T20:01:50-04:00 — #1
What is the best way to move [selected] data from a SQL Server database into a MySQL database on a table-by-table, and row-by-row basis?
This project is a forum software conversion.
Both of the databases are on the same Windows Server. The current SQL Server database is 2362.94 megabytes (2.3 gigabytes) in size.
This isn't a simple conversion of one type to another - I would like to "single out" and "sort out" which columns from which tables I need.
Just a question, and I appreciate any replies.
r937 — 2011-06-29T20:52:11-04:00 — #2
the way i would approach it is to choose whichever database i preferred to do my work in
if this were sql server, i would run some queries to extract and reformat the data i want, ending up with it in a special new table that exactly mimics the format of the target mysql table, then export it, upload it to mysql, and vwalah
on the other hand, since i like mysql a lot more, i would just export the raw sql server data, upload the table into mysql, and then do my extracting and reformatting into the final target table with mysql queries
based on the size involved, i might just go with the former
doug_g — 2011-06-30T00:22:11-04:00 — #3
Another option is to use Access as a middleman. In a past life I created a couple data conversion apps within access and having VBA available to "custom tweak" data conversion was helpful.
Access may choke badly on the size of your existing db though.
wwb_99 — 2011-06-30T11:11:26-04:00 — #4
Access might have a fighting chance if you use linked tables rather than actually importing the data. If you want to punish yourself repeatedly, you could try SSIS as an import tool.
Personally, I would write a custom conversion tool that could stream the updates in and do special handling. Would work in any language that spoke both SQL dialects, which is pretty much anything these days. Main advantage is you can handle funny edge cases and do other data manipulation and error handling stunts that would be difficult in SQL directly.
doug_g — 2011-06-30T23:37:47-04:00 — #5
Access might have a fighting chance if you use linked tables rather than actually importing the data.
Yep, that's what I used. It was back in the 2003 days, I used an Access Data Project that is just a front-end to sql server, linked tables from the target DB and Access just served as a tool and IDE for writing custom VBA code to work with the data. It worked pretty well, in effect it was a "custom conversion tool" in VB with a bunch of built-in data interfaces so I didn't have to write that part and could focus on the conversion algorithms.
balmoral — 2011-07-02T17:57:21-04:00 — #6
Thanks for the responses.
I have decided to write a custom conversion script, or multiple scripts for different tables. The reason for this is that the structures of the starting database and the final database will be entirely different. I have figured it would be a greater challenge reorganising such data in this way.
Given this, what is the most appropriate (or best) language to use for this; one that can connect to both databases? I realise that might be a subjective question but I really am only looking for opinions on this one.