Inserting from MS SQL Server to MySQL database

Question

I want to create a stored procedure that INSERTs data from the MSSQL DB tables to my MySQL DB tables and vice versa. What do I need to do this?

I have looked into two solutions.

Creating a linked server that will allow me to write and Query data from MySQL using stored procedures in MSSQL.
MySQL Migration Toolkits. How ever this option seems like an overkill, since I only want to make a few INSERTS and SELECTS between the two

So I lay my trust in you and hope someone has had similar problems in the past and would be so kind and give me the best way to do this. Especially would the 1st option work?

Who ever helps me will have my undying gratitude.

EDIT: Forgot to mention they reside in different servers (running debian and MS)

Have you considered dumping the MSSQL data into csv, then import it into Mysql? — Mad Dog Tannen
– Mad Dog Tannen, Commented May 22, 2014 at 11:38
Dumping data would not work, since I need to triggered or timed procedure that will run every 5 min. It would create unneeded steps in the process. For the version info: SQL Server 2008 R2 Service Pack 2 and 5.5.37-0+wheezy1 protocol v: 10 — user1054844
– user1054844, Commented May 22, 2014 at 12:19
No, it will be multiple times per hour. I need the 2 databases to be as identical as possible at all times 5-15 minute latency can be tolerated — user1054844
– user1054844, Commented May 22, 2014 at 12:26

Pred · Accepted Answer · 2016-08-10 11:50:23Z

21

If you want to do this regulary

LinkedServer and OPENQUERY could be good, if you are moving not too much records per cycle.
Integration Services Package is a good solution if you want to move lots of data (like thousands or millions of rows).

You can schedule them with SQL Server Agent.

The INSERT is a bit tricky with OPENQUERY:

INSERT INTO OPENQUERY (MYSQL, 'SELECT * FROM MySQL_Table')
SELECT * FROM dbo.SQLServer_Table

OR

INSERT INTO OPENQUERY (MYSQL, 'SELECT * FROM MySQL_Table')
VALUES ('Value1', 'Value2', ...)

edited Aug 10, 2016 at 11:50

answered May 22, 2014 at 12:27

Pred

9,0903 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1054844 Over a year ago

Thanks. So I can query MySQL tables with MSSQL T-SQL syntaxes without worrying too much about the compability after linking them? My biggest worry is writing the queries and then having to find out that some of the basic commands like INSERT, LEFT JOIN, UPDATE would not be compatible, because of the syntax issues...

Pred Over a year ago

You have to use the MySQL syntax in the OPENQUERY, and this stands to source components in SSIS to. T-SQL syntax won't work, inside the OPENQUERY, but outside of it, you should use T-SQL. I've edited the answer to show how to insert into MySQL with OPENQUERY

insoftservice Over a year ago

@Pred how to dump mssql data from one server to mysql of another server

Pred Over a year ago

@insoftservice That is something you usually cannot do easily and automatically. See the questions already asked in this topic. Also your question is extending the scope of the current one and should be asked separately. Please read How to Ask in help center.

Collectives™ on Stack Overflow

Inserting from MS SQL Server to MySQL database

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related