CODEWITHSUNDEEP
mysqlsqlsql-serverdata-migration
spas2k
spas2k

Reputation: 519

How to copy a table from SQL Server into MySQL?

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.

Upvotes: 4

Views: 10213

Answers (4)

Chris Travers
Chris Travers

Reputation: 26474

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.

Upvotes: 3

Luke
Luke

Reputation: 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.

Upvotes: -1

nitin.sharma0180
nitin.sharma0180

Reputation: 481

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.

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 563011

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:

Upvotes: 0

Related Questions