使用事务的最佳实践：SQL Server批量插入失败时是否需回滚？

这个问题的核心其实取决于你对TABLEB数据一致性的要求，咱们分两种常见场景来拆解：

场景1：要求数据绝对一致，不能有部分插入 #

首先得明确一个默认行为：你写的INSERT INTO TABLEB (SELECT NAME, ID FROM TableA)这种单条批量插入语句，在SQL Server里本身就是原子操作——要么100万条全成功插入，要么中间某条记录出错（比如违反唯一键约束、数据类型不匹配），整个语句直接终止，已经插入的行也会被自动回滚，不会留下半完成的数据。

那为啥还要显式加事务？如果你的存储过程未来可能扩展操作（比如插入后要更新TableA的状态、写入同步日志表），显式事务能把这些操作和INSERT绑定成一个原子单元，确保所有步骤要么都成功，要么全回滚，避免出现“日志写了但数据没插进去”这种不一致的情况。

推荐的存储过程写法会加上事务、TRY/CATCH和XACT_ABORT（确保出错时自动回滚）：

CREATE PROCEDURE BULKINSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 关键设置：出错时自动终止并回滚事务

    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO TABLEB (NAME, ID)
        SELECT NAME, ID FROM TableA;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        -- 这里可以加错误日志逻辑，比如把错误信息写入专门的日志表
        THROW; -- 抛出错误，让调用方知道失败原因
    END CATCH
END
GO

这种情况下，必须回滚整个操作，才能保证TABLEB的数据完整性。

场景2：允许保留成功插入的行，仅跳过失败记录 #

如果你的业务逻辑允许TABLEB先存能成功插入的行，跳过出错的记录，那完全不需要回滚整个操作，反而要调整插入逻辑来过滤或跳过错误行。

举两个常见的实现方式：

1. 提前过滤可能出错的行（比如唯一键冲突）：

CREATE PROCEDURE BULKINSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO TABLEB (NAME, ID)
    SELECT NAME, ID FROM TableA
    WHERE NOT EXISTS (SELECT 1 FROM TABLEB WHERE TABLEB.ID = TableA.ID);
END
GO

2. 针对唯一键冲突，给目标表的唯一索引设置IGNORE_DUP_KEY = ON（仅对唯一索引生效），这样插入时遇到重复键只会跳过该行，不会终止整个操作：

-- 先给TABLEB的ID列创建带IGNORE_DUP_KEY的唯一索引
CREATE UNIQUE NONCLUSTERED INDEX IX_TABLEB_ID ON TABLEB(ID) WITH (IGNORE_DUP_KEY = ON);

-- 存储过程不变，直接执行插入
CREATE PROCEDURE BULKINSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TABLEB (NAME, ID)
    SELECT NAME, ID FROM TableA;
END
GO

注意：这种方式要做好错误记录，否则你不知道哪些行插入失败了。

总结 #

• 若要求数据强一致性，不能有部分插入：即使默认单条INSERT是原子的，也建议显式添加事务（配合TRY/CATCH和XACT_ABORT），此时必须回滚整个操作。
• 若允许部分插入，仅跳过失败行：不需要回滚，调整插入逻辑过滤错误行即可，但要做好错误日志。

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