SQL Server中如何修改存储过程实现仅插入完成后显示最新记录数

这个需求的核心是确保数据加载操作的原子性——所有新记录要么全部对用户可见，要么完全不可见，不会出现部分数据提前暴露的情况。针对SQL Server环境（因为你提到了SSIS包，默认搭配SQL Server），我们只需要对LoadRecords存储过程做以下修改：

1. 用显式事务包裹全部插入逻辑 #

把所有向RecordsTbl插入数据的代码放在一个显式事务中，只有当所有插入操作成功完成后才提交事务；如果中途出错，则回滚所有修改。

2. 添加错误处理机制 #

确保在插入过程中出现异常时，能及时回滚事务，避免事务悬挂或数据不一致。

修改后的存储过程示例 #

CREATE OR ALTER PROCEDURE LoadRecords
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 关键：遇到严重错误时自动回滚事务

    BEGIN TRY
        -- 开启事务
        BEGIN TRANSACTION;

        -- 这里替换成你实际的插入逻辑（比如从源表读取数据插入RecordsTbl）
        -- 示例批量插入：
        INSERT INTO RecordsTbl (Column1, Column2, Column3)
        SELECT SourceColumn1, SourceColumn2, SourceColumn3
        FROM YourSourceTable;

        -- 如果是分批插入（比如处理超大量数据），所有批次都要放在事务内部
        -- WHILE @BatchNumber <= @TotalBatches
        -- BEGIN
        --     INSERT INTO RecordsTbl (...)
        --     SELECT ... FROM YourSourceTable WHERE BatchId = @BatchNumber;
        --     SET @BatchNumber += 1;
        -- END

        -- 所有插入完成后提交事务
        COMMIT TRANSACTION;
        PRINT '数据加载完成，新记录已全部提交';
    END TRY
    BEGIN CATCH
        -- 如果事务处于活跃状态，立即回滚
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 抛出错误，让SSIS包能捕获到异常并处理
        THROW;
    END CATCH
END;

原理说明 #

SQL Server默认的隔离级别是读提交（Read Committed），在这个级别下：

• 未提交的事务修改对其他会话是完全不可见的
• 只有当事务成功提交后，所有新插入的记录才会对所有查询会话可见

这样就能完美满足你的需求：

• 包运行完成（事务提交）后，用户查询SELECT COUNT(1) FROM RecordsTbl能看到最新记录数
• 加载过程中（事务未提交），用户查询只能看到上次加载完成后的记录数

额外注意事项 #

• SET XACT_ABORT ON;是必须的，它能确保在执行过程中遇到严重错误（比如约束冲突、连接中断）时，自动回滚事务，避免事务处于未完成状态。
• 如果你的数据量极大（数千万级），大事务可能会导致事务日志暴涨，此时可以考虑分批插入但仍保持在同一个事务内，既控制日志增长，又保证原子性。
• 不要修改数据库的隔离级别（除非有特殊需求），默认的读提交已经能满足这个场景。

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