C#批量复制SQL表失败时如何避免原表被清空？

这个问题我之前做数据同步的时候也踩过坑——先清空原表再批量插入的流程，一旦BulkCopy中途抛错，原表直接变空，简直是数据安全的噩梦。给你两个经过实践验证的解决方案，你可以根据自己的场景来选：

方案1：用事务包裹全流程，保证原子性 #

核心逻辑是把「清空原表」和「批量插入」绑定到同一个事务里，任何一步失败就立刻回滚，原表的数据会恢复到操作前的状态。

需要注意的是，SqlBulkCopy支持绑定到现有事务，创建实例时一定要传入事务对象，否则批量操作会在独立事务里执行，起不到原子性的作用。

示例代码：

using (var connection = new SqlConnection("你的数据库连接字符串"))
{
    connection.Open();
    // 开启事务
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            // 1. 清空原表（TRUNCATE比DELETE高效，适合全量替换场景）
            var truncateCmd = new SqlCommand("TRUNCATE TABLE YourTargetTableName", connection, transaction);
            truncateCmd.ExecuteNonQuery();

            // 2. 执行批量复制，绑定到当前事务
            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.DestinationTableName = "YourTargetTableName";
                // 显式映射列（列名一致也建议写，避免后续表结构变更出问题）
                foreach (DataColumn col in yourSourceDataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                }
                bulkCopy.WriteToServer(yourSourceDataTable);
            }

            // 所有操作成功，提交事务
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // 出错立刻回滚，原表数据不受影响
            transaction.Rollback();
            // 记录日志后抛出异常，上层可以做后续处理
            throw new InvalidOperationException("批量插入失败，已回滚所有操作", ex);
        }
    }
}

方案优缺点：

• ✅ 优点：实现简单，不需要额外表结构；原子性强，要么全成功要么全回滚。
• ❌ 缺点：如果原表数据量极大，事务会占用较多数据库资源（锁、日志），长时间运行可能影响其他业务操作。

方案2：用临时表过渡，完全隔离原表数据 #

核心思路是先把数据批量插入到临时表（或中间 staging 表），确认插入成功后，再替换原表的数据。这种方式下，即使临时表插入失败，原表的数据毫发无损，是大数据量场景下更稳妥的选择。

示例代码：

using (var connection = new SqlConnection("你的数据库连接字符串"))
{
    connection.Open();
    try
    {
        // 1. 创建会话级临时表（#开头，会话结束自动销毁），复制原表结构
        var createTempTableCmd = new SqlCommand(@"
            IF OBJECT_ID('tempdb..#TempTargetTable') IS NOT NULL
                DROP TABLE #TempTargetTable;
            -- 复制原表结构，不复制数据
            SELECT * INTO #TempTargetTable FROM YourTargetTableName WHERE 1=0;
        ", connection);
        createTempTableCmd.ExecuteNonQuery();

        // 2. 批量插入数据到临时表
        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "#TempTargetTable";
            foreach (DataColumn col in yourSourceDataTable.Columns)
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }
            bulkCopy.WriteToServer(yourSourceDataTable);
        }

        // 3. 原子替换原表数据：用事务包裹清空+插入（或用表交换）
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                // 清空原表
                var truncateCmd = new SqlCommand("TRUNCATE TABLE YourTargetTableName", connection, transaction);
                truncateCmd.ExecuteNonQuery();

                // 从临时表同步数据到原表
                var insertCmd = new SqlCommand(@"
                    INSERT INTO YourTargetTableName
                    SELECT * FROM #TempTargetTable;
                ", connection, transaction);
                insertCmd.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new InvalidOperationException("替换原表数据失败，已回滚", ex);
            }
        }
    }
    catch (Exception ex)
    {
        // 这里处理临时表插入失败的情况，原表完全不受影响
        throw new InvalidOperationException("批量插入临时表失败", ex);
    }
}

进阶优化：如果你的SQL Server版本支持，可以用ALTER TABLE ... SWITCH替换「TRUNCATE+INSERT」，性能提升极大（几乎是瞬间完成），前提是临时表和原表结构完全一致（包括索引、约束）：

ALTER TABLE #TempTargetTable SWITCH TO YourTargetTableName;

方案优缺点：

• ✅ 优点：原表数据全程隔离，风险极低；大数据量下性能更优（尤其用表交换）。
• ❌ 缺点：需要同步维护临时表的结构（原表变更时临时表也要跟着改）；代码相对复杂一点。

额外注意事项 #

• 无论用哪种方案，都要详细记录异常日志，方便后续排查问题。
• 如果原表有外键约束，要么在事务中先禁用约束（完成后再启用），要么确保插入的数据符合外键规则，避免报错导致回滚。
• 对于超大规模的数据同步，优先选方案2，避免长时间锁住原表影响业务。

内容的提问来源于stack exchange，提问作者pato.llaguno