如何将 Excel 多表持久化逻辑迁移到 SQL Server?
数据小陈
结论:可行。更稳妥的做法是让应用侧只把原始数据落入暂存表,再由 SQL Server 用显式存储过程完成多表拆分和父子表写入。核心逻辑不建议长期放在触发器里。SqlBulkCopy 的作用只是高性能批量插入,它没法直接把 Excel 拆到多张业务表;用了它也不代表项目里完全不需要 DbContext,只是这条导入链路一般会绕过 EF Core 的变更跟踪。
为什么可行
SQL Server 的存储过程可以做集合式处理:先把 Excel 原始列写进一张不带外键的暂存表,再按业务键插入或更新父表,最后 JOIN 父表拿到外键,写入子表和联结表。父表先写、子表后写,外键约束就能满足。触发器确实也能调用存储过程,但显式调用更可控,错误处理、事务边界、重复执行和测试都更好处理。
推荐流程
- 用 Sylvan.Data.Excel 读取 Excel,得到 DbDataReader。
- 用 SqlBulkCopy 把原始行写入 staging 表;字段尽量保持 Excel 原样,并加上行号或导入批次号。
- 在 SQL Server 里做校验、去重、空值处理和错误记录。
- 存储过程按顺序执行:先 Category,再 Product,然后 SubCategory,最后是 ProductCategory 等联结表。
- 整个导入放在事务里;失败回滚,成功后清理暂存表,或者保留批次日志。
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#,把校验留在应用层、批量写入放到数据库侧,通常更平衡。
备注:内容仅供参考。
