PAYATHON 2026

如何将 Excel 多表持久化逻辑迁移到 SQL Server?

数据小陈

结论:可行。更稳妥的做法是让应用侧只把原始数据落入暂存表,再由 SQL Server 用显式存储过程完成多表拆分和父子表写入。核心逻辑不建议长期放在触发器里。SqlBulkCopy 的作用只是高性能批量插入,它没法直接把 Excel 拆到多张业务表;用了它也不代表项目里完全不需要 DbContext,只是这条导入链路一般会绕过 EF Core 的变更跟踪。

为什么可行

SQL Server 的存储过程可以做集合式处理:先把 Excel 原始列写进一张不带外键的暂存表,再按业务键插入或更新父表,最后 JOIN 父表拿到外键,写入子表和联结表。父表先写、子表后写,外键约束就能满足。触发器确实也能调用存储过程,但显式调用更可控,错误处理、事务边界、重复执行和测试都更好处理。

推荐流程

  1. 用 Sylvan.Data.Excel 读取 Excel,得到 DbDataReader。
  2. 用 SqlBulkCopy 把原始行写入 staging 表;字段尽量保持 Excel 原样,并加上行号或导入批次号。
  3. 在 SQL Server 里做校验、去重、空值处理和错误记录。
  4. 存储过程按顺序执行:先 Category,再 Product,然后 SubCategory,最后是 ProductCategory 等联结表。
  5. 整个导入放在事务里;失败回滚,成功后清理暂存表,或者保留批次日志。

C# 示例

using Microsoft.Data.SqlClient;
using Sylvan.Data.Excel;

await using var stream = File.OpenRead("products.xlsx");
using var excel = ExcelDataReader.Create(stream);

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var tx = await connection.BeginTransactionAsync();

using var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, (SqlTransaction)tx)
{
    DestinationTableName = "dbo.ExcelProductStaging",
    BatchSize = 5000,
    BulkCopyTimeout = 0
};

bulk.ColumnMappings.Add("SKU", "SKU");
bulk.ColumnMappings.Add("BarCode", "BarCode");
bulk.ColumnMappings.Add("EnglishName", "EnglishName");
bulk.ColumnMappings.Add("ArabicName", "ArabicName");
bulk.ColumnMappings.Add("Category", "Category");
bulk.ColumnMappings.Add("SubCategory", "SubCategory");

await bulk.WriteToServerAsync(excel);

await using var cmd = new SqlCommand("dbo.usp_ImportExcelProducts", connection, (SqlTransaction)tx)
{
    CommandType = CommandType.StoredProcedure,
    CommandTimeout = 0
};

await cmd.ExecuteNonQueryAsync();
await tx.CommitAsync();

存储过程示例

CREATE OR ALTER PROCEDURE dbo.usp_ImportExcelProducts
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    IF EXISTS (SELECT 1 FROM dbo.ExcelProductStaging WHERE SKU IS NULL OR Category IS NULL)
        THROW 50001, 'Staging data validation failed', 1;

    MERGE dbo.Category AS t
    USING (SELECT DISTINCT Category AS EnglishName FROM dbo.ExcelProductStaging WHERE Category IS NOT NULL) AS s
        ON t.EnglishName = s.EnglishName
    WHEN NOT MATCHED THEN
        INSERT (EnglishName) VALUES (s.EnglishName);

    MERGE dbo.Product AS t
    USING (SELECT DISTINCT SKU, BarCode, EnglishName, ArabicName FROM dbo.ExcelProductStaging) AS s
        ON t.SKU = s.SKU
    WHEN NOT MATCHED THEN
        INSERT (SKU, Barcode, EnglishName, ArabicName)
        VALUES (s.SKU, s.BarCode, s.EnglishName, s.ArabicName);

    INSERT dbo.SubCategory (EnglishName, CategoryId)
    SELECT DISTINCT s.SubCategory, c.CategoryId
    FROM dbo.ExcelProductStaging s
    JOIN dbo.Category c ON c.EnglishName = s.Category
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.SubCategory sc
        WHERE sc.EnglishName = s.SubCategory AND sc.CategoryId = c.CategoryId
    );

    INSERT dbo.ProductCategory (ProductID, CategoryId)
    SELECT DISTINCT p.ProductID, c.CategoryId
    FROM dbo.ExcelProductStaging s
    JOIN dbo.Product p ON p.SKU = s.SKU
    JOIN dbo.Category c ON c.EnglishName = s.Category
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.ProductCategory pc
        WHERE pc.ProductID = p.ProductID AND pc.CategoryId = c.CategoryId
    );

    COMMIT;
END;

优点和缺点

好处是批量写入吞吐高、应用内存占用低、网络往返少;关系解析在数据库内完成,可以用事务、索引和集合操作;导入逻辑也能复用。代价是 T-SQL 复杂之后测试和调试成本会上升,业务规则容易和数据库耦合;触发器有隐式执行和重复触发的风险;自增主键映射、唯一键、并发和幂等都要额外设计;换数据库时移植性也差。

注意事项

staging 表不要加业务外键,只保存原始列和批次信息;给 SKU、Category、SubCategory 这类业务键建唯一索引,或者至少做去重;SqlBulkCopy 的列映射必须和 staging 表一致;只有用了 FireTriggers 才会触发触发器,但更建议显式执行存储过程;生产环境要记录错误行、控制事务大小,并考虑同一文件重复导入时的幂等策略。如果业务规则经常变化、团队更熟悉 C#,把校验留在应用层、批量写入放到数据库侧,通常更平衡。

备注:内容仅供参考。