T-SQL批量插入后无法逐次打印提示，如何实现即时输出？

在SQL Server中执行多条INSERT批量插入操作时，希望每次插入完成后立即打印成功或失败的提示语句，但当前代码的提示信息会在所有插入操作全部完成后才一并输出，无法实现逐次即时显示。现有T-SQL代码如下：

-- Token 1
BEGIN TRANSACTION;
BEGIN TRY

    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_1', token_1 
    FROM crim_justice_tokens
    WHERE (id is not null) and (token_1 NOT LIKE '%XXX -%')

    COMMIT TRANSACTION;
    RAISERROR ('Token 1 completed successfully', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    -- Print the error message
    RAISERROR ('Error inserting Token 1, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

-- Token 2
BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_2', token_2 
    FROM crim_justice_tokens
    WHERE (id is not null) and (token_2 NOT LIKE '%XXX -%')

    COMMIT TRANSACTION;
    RAISERROR ('Token 2 completed successfully', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    -- Print the error message
    RAISERROR ('Error inserting Token 2, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

核心调整点有两个，同时修复代码中未声明变量的问题：

• 调整RAISERROR的消息级别：将级别从0改为10（普通信息级别，不会触发异常），配合WITH NOWAIT强制客户端即时输出消息，避免缓冲。
• 添加SET NOCOUNT ON：关闭默认的影响行数输出，减少客户端缓冲队列的占用，加速提示消息的推送。

修改后的完整代码：

SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(4000);

-- Token 1
BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_1', token_1 
    FROM crim_justice_tokens
    WHERE (id IS NOT NULL) AND (token_1 NOT LIKE '%XXX -%');

    COMMIT TRANSACTION;
    RAISERROR ('Token 1 completed successfully', 10, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();
    ROLLBACK TRANSACTION;

    RAISERROR ('Error inserting Token 1, rolling back', 10, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 10, 1) WITH NOWAIT;
END CATCH;

-- Token 2
BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_2', token_2 
    FROM crim_justice_tokens
    WHERE (id IS NOT NULL) AND (token_2 NOT LIKE '%XXX -%');

    COMMIT TRANSACTION;
    RAISERROR ('Token 2 completed successfully', 10, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();
    ROLLBACK TRANSACTION;

    RAISERROR ('Error inserting Token 2, rolling back', 10, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 10, 1) WITH NOWAIT;
END CATCH;

另外，如果你使用的是SQL Server 2012及以上版本，也可以用THROW替代RAISERROR，但要注意THROW的语法差异；不过RAISERROR ... WITH NOWAIT在即时输出场景下依然是更直接的选择。

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