如何用单条SQL语句批量插入父/子表（MainItem与SubItem）数据

嘿，看你现在在处理批量插入主从表数据的问题——几千条MainItem每条对应几十条SubItem，原来的循环逐条插入方式肯定效率拉胯，我给你整理了清晰的优化方案，先理清楚基础信息：

表结构定义 #

MainItem表 #

SubItem表 #

你当前实现的问题 #

从你给出的代码片段来看，你是通过循环逐条插入MainItem，再循环插入对应SubItem，还要维护旧ID到新ID的字典映射。这种方式面对几千条主记录+数万条子记录时，性能会非常差——每一条INSERT都是一次数据库往返，网络开销和数据库的连接、事务开销会把整体速度拖慢到难以接受的程度。

Dictionary<int, int> oldToNewIDs = new Dictionary<int, int>();
foreach(MainItem mainItem in GeneratedMainItems) 
{
    int oldID = mainItem.ID;
    mainItem.Update(... // 这里应该是单条插入MainItem的逻辑
    // 获取数据库生成的新自增ID
    int newID = ...;
    oldToNewIDs.Add(oldID, newID);
    
    // 逐条插入关联的SubItem
    foreach(SubItem subItem in GeneratedSubItems.Where(s => s.MainItemID == oldID))
    {
        subItem.MainItemID = newID;
        subItem.Insert(...); // 单条插入SubItem
    }
}

推荐的优化方案 #

下面给你两种高效的批量插入方案，都只需要几次数据库往返就能完成所有数据插入：

方案1：表值参数（TVP）+ OUTPUT子句（最推荐） #

这个方案可以一次性插入所有MainItem，同时直接获取旧ID和新自增ID的映射，再批量插入SubItem，是性能最优的选择。

第一步：创建SQL Server表值参数类型

先在数据库里创建两个自定义表类型，用来传递批量数据：

-- 用于传递MainItem批量数据的类型
CREATE TYPE dbo.MainItemBatchType AS TABLE
(
    OldID int,
    Total money
);

-- 用于传递SubItem批量数据的类型
CREATE TYPE dbo.SubItemBatchType AS TABLE
(
    NewMainItemID int,
    Price money
);

第二步：C#代码实现批量插入

// 1. 准备MainItem的批量数据
DataTable mainBatchTable = new DataTable();
mainBatchTable.Columns.Add("OldID", typeof(int));
mainBatchTable.Columns.Add("Total", typeof(decimal)); // C#里decimal对应SQL的money类型

foreach (var mainItem in GeneratedMainItems)
{
    mainBatchTable.Rows.Add(mainItem.ID, mainItem.Total);
}

Dictionary<int, int> oldToNewIdMap = new Dictionary<int, int>();

using (SqlConnection conn = new SqlConnection("你的数据库连接字符串"))
{
    conn.Open();

    // 2. 批量插入MainItem并获取新旧ID映射
    using (SqlCommand insertMainCmd = new SqlCommand(@"
        DECLARE @IdMapping TABLE (NewID int, OldID int);
        INSERT INTO MainItem (Total)
        OUTPUT inserted.ID, mi.OldID INTO @IdMapping
        SELECT Total FROM @MainItems mi;
        SELECT NewID, OldID FROM @IdMapping;", conn))
    {
        // 添加表值参数
        SqlParameter mainParam = insertMainCmd.Parameters.AddWithValue("@MainItems", mainBatchTable);
        mainParam.SqlDbType = SqlDbType.Structured;
        mainParam.TypeName = "dbo.MainItemBatchType";

        // 读取映射结果并填充字典
        using (SqlDataReader reader = insertMainCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                int newId = reader.GetInt32(0);
                int oldId = reader.GetInt32(1);
                oldToNewIdMap.Add(oldId, newId);
            }
        }
    }

    // 3. 准备SubItem的批量数据
    DataTable subBatchTable = new DataTable();
    subBatchTable.Columns.Add("NewMainItemID", typeof(int));
    subBatchTable.Columns.Add("Price", typeof(decimal));

    foreach (var subItem in GeneratedSubItems)
    {
        if (oldToNewIdMap.TryGetValue(subItem.MainItemID, out int newMainId))
        {
            subBatchTable.Rows.Add(newMainId, subItem.Price);
        }
    }

    // 4. 批量插入SubItem
    using (SqlCommand insertSubCmd = new SqlCommand(@"
        INSERT INTO SubItem (MainItemID, Price)
        SELECT NewMainItemID, Price FROM @SubItems;", conn))
    {
        SqlParameter subParam = insertSubCmd.Parameters.AddWithValue("@SubItems", subBatchTable);
        subParam.SqlDbType = SqlDbType.Structured;
        subParam.TypeName = "dbo.SubItemBatchType";

        insertSubCmd.ExecuteNonQuery();
    }
}

方案2：SqlBulkCopy + 临时标识字段 #

如果因为权限问题无法创建表值参数类型，可以用SqlBulkCopy先批量插入MainItem，再通过临时标识字段获取新旧ID映射。

第一步：给MainItem表添加临时字段（可选，用完可删除）

ALTER TABLE MainItem ADD TempGuid uniqueidentifier;

第二步：C#代码实现

// 给每个生成的MainItem添加临时GUID标识
foreach (var mainItem in GeneratedMainItems)
{
    mainItem.TempGuid = Guid.NewGuid();
}

// 1. 批量插入MainItem
DataTable mainBulkTable = new DataTable();
mainBulkTable.Columns.Add("TempGuid", typeof(Guid));
mainBulkTable.Columns.Add("Total", typeof(decimal));

foreach (var mainItem in GeneratedMainItems)
{
    mainBulkTable.Rows.Add(mainItem.TempGuid, mainItem.Total);
}

using (SqlBulkCopy bulkCopy = new SqlBulkCopy("你的数据库连接字符串"))
{
    bulkCopy.DestinationTableName = "MainItem";
    bulkCopy.ColumnMappings.Add("TempGuid", "TempGuid");
    bulkCopy.ColumnMappings.Add("Total", "Total");
    bulkCopy.WriteToServer(mainBulkTable);
}

// 2. 获取新旧ID映射
Dictionary<int, int> oldToNewIdMap = new Dictionary<int, int>();
using (SqlConnection conn = new SqlConnection("你的数据库连接字符串"))
{
    conn.Open();
    using (SqlCommand getMapCmd = new SqlCommand(@"
        SELECT ID, OldID FROM MainItem 
        WHERE TempGuid IN @TempGuids;", conn))
    {
        // 传递所有临时GUID
        getMapCmd.Parameters.AddWithValue("@TempGuids", GeneratedMainItems.Select(x => x.TempGuid).ToList());
        
        using (SqlDataReader reader = getMapCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                oldToNewIdMap.Add(reader.GetInt32(1), reader.GetInt32(0));
            }
        }
    }
}

// 3. 批量插入SubItem
DataTable subBulkTable = new DataTable();
subBulkTable.Columns.Add("NewMainItemID", typeof(int));
subBulkTable.Columns.Add("Price", typeof(decimal));

foreach (var subItem in GeneratedSubItems)
{
    if (oldToNewIdMap.TryGetValue(subItem.MainItemID, out int newMainId))
    {
        subBulkTable.Rows.Add(newMainId, subItem.Price);
    }
}

using (SqlBulkCopy subBulkCopy = new SqlBulkCopy("你的数据库连接字符串"))
{
    subBulkCopy.DestinationTableName = "SubItem";
    subBulkCopy.ColumnMappings.Add("NewMainItemID", "MainItemID");
    subBulkCopy.ColumnMappings.Add("Price", "Price");
    subBulkCopy.WriteToServer(subBulkTable);
}

// 可选：删除临时字段
// using (SqlCommand dropColCmd = new SqlCommand("ALTER TABLE MainItem DROP COLUMN TempGuid;", conn))
// {
//     conn.Open();
//     dropColCmd.ExecuteNonQuery();
// }

方案优势对比 #

• 表值参数方案：代码更简洁，不需要修改表结构，性能最优，推荐优先使用。
• SqlBulkCopy方案：适合无法创建自定义表类型的场景，但需要临时字段或其他唯一标识来关联新旧ID。

不管用哪种方案，都能把原来的数千次数据库往返减少到3次以内，性能提升非常显著。

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