3

What is the best way to copy a table from a SQL Server into a mysql server on a different machine? I need join the data between from the sql server onto the mysql server but just want to see if anyone had some better ideas than I do, which is currently at simply writing a script and copying the table row by row with inserts. SQL table is about 100k rows.

tadman
  • 208,517
  • 23
  • 234
  • 262
spas2k
  • 499
  • 1
  • 6
  • 15
  • On sql server, set up a linked server to MySQL. Write the necessary queries/stored procedures in SSMS, or perhaps, this will work. https://en.wikipedia.org/wiki/MySQL_DataController – Dan Bracuk Sep 20 '17 at 17:23
  • Easiest? [Navicat](http://navicat.com). Cheapest? Dump as CSV and restore. – tadman Sep 20 '17 at 17:24
  • Use dbis. easy to use and you can change column mapping and configuration any time. https://dbisweb.wordpress.com/ – compyutech Apr 26 '18 at 17:40

4 Answers4

3

You have a few options:

  1. Set up a linked servers on SQL Server against the MySQL db, and then join the data as needed on the SQL Server side
  2. Having set up the linked server, you can also copy the table over to a new location using standard SQL (or create a table in MySQL and copy data in).
  3. You can also use Data Transformation Services in SQL Server to export into a csv or similar, to import in MySQL using a csv import tool.

Which one is best depends to some extent on size of the record set and how much of it you need. My preference would be to use DTS and go from there.

Mat
  • 202,337
  • 40
  • 393
  • 406
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • I can't do this on the sql server. It has to be the mysql server. I don't have direct and full access to the sql server so I have to do it the other way around. – spas2k Sep 20 '17 at 17:39
  • In that case asking someone for a csv dump who does have full access is likely to be the best way forward. If that fails then Excel may be a useful tool for dumping to CSV if it is small enough. – Chris Travers Sep 20 '17 at 17:41
0

It would be better to do this using bulk export and import operations, instead of row by row.

Here are references to some relevant Stack Overflow posts that describe how to do each step:

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
-1

You can try it with SQL Server Management Studio by using the Export Import Wizard.

SQL Wizard

In this case, you will use the SQL Server database table as the source, and the MySQL database table as the destination.

nitin.sharma0180
  • 471
  • 3
  • 6
-1

Many server administration tools including SQL Server Management Studio for Microsoft SQL server and phpMyAdmin include tools to export a tables content into another form such as XML,json, csv, and so on. They also allow one to import data from the same types of files. This would be the simplest route, especially with just one table.

Another option is an ETL tool. If you are familiar with Java you could use an open source tool such as Talend Open Studio for Data Integration to create jobs that can configure connections and a job that will automatically move or copy the data from one database to another, and also gives you more control if you need to filter or change any of the data along the way.

Luke
  • 1