How to transfer Data automaticly between MySql and Sql Server back and fourth?

Question

I need to build a procedure to read data from SQL Server and insert it into MySql. More I also need another procedure to read data from MySql Server and insert it into SQL Server.

I never done such a thing. I can't think of a way to get it done. Can someone tell me how can I do so such a thing?

I appreciate at least an idea where I can search it.

I have been googling this but did not find much of help.

I have Scribe software I am not sure if there is a future in Scribe that can help me with this?

Note this procedure Will be automatically run so transfer data or receive data from the source.

Answer 1

The best way to achieve this is to create a Linked Server in SQL Server, this way you can query both SQL Server and MySQL from the same place. You can write a stored procedure to access both or a Job to run on specific schedule.

Update: Sample INSERT Query

INSERT OPENQUERY (SERVNAME, 'SELECT subject, notes FROM cms.mysql_table') 
SELECT subject, notes FROM sql_server_table