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.

1. Creating a linked server that will allow me to write and Query data from MySQL using stored procedures in MSSQL.

2. 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)

Answer 1

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', ...)