如何通过存储过程或SSIS在SQL Server中创建与MySQL同结构的批量表

方法一：使用 T-SQL 生成建表语句 #

通过链接服务器读取 MySQL 的元数据，动态生成符合 SQL Server 语法的建表脚本，步骤如下：

1. 创建 MySQL 链接服务器
   先确保已安装 MySQL ODBC 驱动，然后执行以下 T-SQL 创建链接服务器：

   EXEC sp_addlinkedserver
     @server = 'MYSQL_LINK',
     @srvproduct = 'MySQL',
     @provider = 'MSDASQL',
     @datasrc = '你的MySQL ODBC数据源名称';
   
   EXEC sp_addlinkedsrvlogin
     @rmtsrvname = 'MYSQL_LINK',
     @useself = 'FALSE',
     @rmtuser = 'MySQL用户名',
     @rmtpassword = 'MySQL密码';
   

2. 生成建表脚本
   查询 MySQL 的 information_schema.columns 获取表结构，转换为 SQL Server 语法，示例脚本：

   DECLARE @TableName NVARCHAR(128) = '你的MySQL表名'; -- 可循环批量处理多个表
   DECLARE @CreateSQL NVARCHAR(MAX) = '';
   
   SELECT @CreateSQL = @CreateSQL + 
     CASE WHEN COLUMN_NAME = (SELECT MIN(COLUMN_NAME) FROM OPENQUERY(MYSQL_LINK, 
       'SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_SCHEMA=''你的MySQL数据库名'' AND TABLE_NAME=''' + @TableName + ''''))
     THEN 'CREATE TABLE [' + @TableName + '] (' + CHAR(13) + CHAR(10) + '  [' + COLUMN_NAME + '] ' +
     ELSE ', ' + CHAR(13) + CHAR(10) + '  [' + COLUMN_NAME + '] ' +
     END +
     -- 数据类型映射
     CASE DATA_TYPE
       WHEN 'int' THEN 'INT'
       WHEN 'varchar' THEN 'VARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR) + ')'
       WHEN 'datetime' THEN 'DATETIME2'
       WHEN 'text' THEN 'VARCHAR(MAX)'
       WHEN 'decimal' THEN 'DECIMAL(' + CAST(NUMERIC_PRECISION AS NVARCHAR) + ',' + CAST(NUMERIC_SCALE AS NVARCHAR) + ')'
       -- 其他类型自行补充映射
       ELSE 'NVARCHAR(MAX)'
     END +
     -- 非空约束
     CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END + CHAR(13) + CHAR(10)
   FROM OPENQUERY(MYSQL_LINK, 
     'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE 
      FROM information_schema.columns 
      WHERE TABLE_SCHEMA=''你的MySQL数据库名'' AND TABLE_NAME=''' + @TableName + ''' 
      ORDER BY ORDINAL_POSITION');
   
   -- 添加主键约束（如果有）
   SELECT @CreateSQL = @CreateSQL + ', PRIMARY KEY (' + STRING_AGG('[' + COLUMN_NAME + ']', ', ') + ')' + CHAR(13) + CHAR(10) + ')'
   FROM OPENQUERY(MYSQL_LINK, 
     'SELECT k.COLUMN_NAME
      FROM information_schema.table_constraints t
      JOIN information_schema.key_column_usage k 
        ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
      WHERE t.TABLE_SCHEMA=''你的MySQL数据库名'' 
        AND t.TABLE_NAME=''' + @TableName + ''' 
        AND t.CONSTRAINT_TYPE=''PRIMARY KEY''
      ORDER BY k.ORDINAL_POSITION');
   
   -- 执行生成的建表语句
   EXEC sp_executesql @CreateSQL;
   

   注意：可通过循环遍历 MySQL 所有表，批量生成建表脚本；需根据实际数据类型补充映射规则，比如 MySQL 的 ENUM 可转换为 SQL Server 的 CHECK 约束。

方法二：使用 SSIS 迁移表结构 #

通过 SSIS 的「传输数据库对象任务」直接复制表结构，步骤如下：

1. 新建 SSIS 项目
   在 SQL Server Data Tools (SSDT) 中创建新的 Integration Services 项目。

2. 配置连接管理器

   • 添加「ODBC 连接管理器」，选择已配置好的 MySQL ODBC 数据源，完成 MySQL 连接配置。
   • 添加「OLE DB 连接管理器」，连接目标 SQL Server 数据库。

3. 添加并配置「传输数据库对象任务」

   • 从工具箱拖放「传输数据库对象任务」到控制流界面。
   • 双击任务打开编辑器：
     • 在「常规」选项卡，选择源连接为 MySQL 的 ODBC 连接，目标连接为 SQL Server 的 OLE DB 连接。
     • 在「对象」选项卡，勾选需要传输的表（可批量选择），设置「复制数据」为False（仅复制结构）。
     • 在「选项」选项卡，根据需要调整对象传输的规则，比如是否复制约束、索引等。

4. 执行任务
   运行 SSIS 包，完成表结构的同步。

注意：SSIS 会自动处理大部分数据类型映射，但部分特殊类型（如 MySQL 的 TEXT、ENUM）可能需要手动调整目标表结构。

内容的提问来源于stack exchange，提问作者Abhishek Nimje