从MS SQL Server插入MySQL数据库 [英] Inserting from MS SQL Server to MySQL database

问题描述

我想创建一个存储过程，将数据从MSSQL DB表插入到我的MySQL DB表中，反之亦然.我需要做什么?

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. 创建一个链接服务器，使我可以使用MSSQL中的存储过程从MySQL写入和查询数据.

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

MySQL迁移工具包.这个选项似乎过大了，因为我只想在两者之间做一些插入和选择操作

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.

忘记提及它们驻留在不同的服务器(运行debian和MS)中

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

推荐答案

如果要定期执行此操作

如果每个周期移动的记录不是太多，则
• LinkedServer 和OPENQUERY可能很好.
如果您要移动大量数据(例如数千行或数百万行)，
• 集成服务包是一个很好的解决方案.

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

您可以使用 SQL Server代理安排它们.

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

这篇关于从MS SQL Server插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！