如何自动执行SELECT生成的CREATE TABLE等SQL语句并支持定时调度

适配场景 #

• 完全兼容当前跨MSSQL、DB2链接服务器的迁移环境
• 同一套逻辑可通用于你编写的所有生成CREATE TABLE/DROP TABLE/INSERT INTO等DDL、DML语句的SELECT查询
• 无人工交互依赖，可直接托管给SQL Agent做定时调度

实现方式 #

核心用本地游标逐行读取你SELECT生成的动态SQL文本，逐行提交执行，不需要手动复制粘贴语句。

注意：拼接动态SQL时所有字符串常量前加N前缀，接收SQL文本的变量用NVARCHAR(MAX)类型，防止长语句截断、非ASCII字符（比如中文备注、特殊表名）乱码。

可直接复用的脚本模板 #

只需要替换代码中标注的SQL生成逻辑段即可，不管是建表、删表还是数据写入的生成逻辑都能直接用：

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @exec_sql NVARCHAR(MAX);

-- 定义游标读取所有待执行的动态SQL
DECLARE sql_cursor CURSOR LOCAL FAST_FORWARD FOR
-- ============== 替换为你自己的SQL生成逻辑 开始 ==============
SELECT
   N'CREATE TABLE [remotedb2server].[remotedb2db].[myschema].'
   + QUOTENAME(t.name)
   + N' COLUMNS HERE AND SOME COMPLEX TRANSFORMATION'
FROM [remotemssqlserver].[remotemssql].[sys].[tables] t
-- 要执行DROP、INSERT逻辑时，直接把上面的SELECT替换成对应语句的生成查询即可
-- ============== 替换区域结束 ==============

OPEN sql_cursor;
FETCH NEXT FROM sql_cursor INTO @exec_sql;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 打印当前执行的语句，会留存到SQL Agent作业历史中，方便排错
    PRINT @exec_sql;

    -- 执行语句，因为生成的SQL已经带了DB2链接服务器前缀，直接调用EXEC即可
    -- 踩坑提示：不要用sp_executesql跑跨DB2链接服务器的DDL，部分OLEDB驱动对参数化支持差，直接EXEC字符串兼容性最高
    EXEC (@exec_sql);
    -- 如果遇到分布式执行权限报错，换成下面这种写法，直接把语句下发到DB2链接服务器端执行：
    -- EXEC (@exec_sql_without_linkserver_prefix) AT [remotedb2server];

    FETCH NEXT FROM sql_cursor INTO @exec_sql;
END

-- 游标清理
CLOSE sql_cursor;
DEALLOCATE sql_cursor;

SQL Agent作业配置要点 #

• 作业步骤类型选「Transact-SQL (T-SQL)」，执行数据库选你本地MSSQL实例上存放该脚本的业务库即可
• 作业运行账号、两个链接服务器的映射账号要提前配好权限：源MSSQL的读权限、目标DB2的DDL操作/数据写入权限
• 作业步骤高级选项里建议开启输出留存，把执行日志存到系统表或者本地文件，出问题可以直接定位到失败的具体语句
• 如果单次执行涉及表多、数据量大，建议加异常捕获，避免单条语句失败导致整个任务中断，参考写法：

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @exec_sql;
    BEGIN TRY
        EXEC (@exec_sql);
    END TRY
    BEGIN CATCH
        PRINT N'执行失败，错误码：' + CAST(ERROR_NUMBER() AS VARCHAR) + N'，错误信息：' + ERROR_MESSAGE();
        PRINT N'失败语句：' + @exec_sql;
        -- 需要单条失败就终止整个任务就放开下面的THROW，不需要就注释，跳过错误继续执行后续语句
        -- THROW;
    END CATCH
    FETCH NEXT FROM sql_cursor INTO @exec_sql;
END

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