SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Oct 2010
    Location
    Chichester, England
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Moving data from SQL Server

    Hullo,

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  6. #6
    SitePoint Member
    Join Date
    Oct 2010
    Location
    Chichester, England
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •