使用SqlBulkCopy向SQL Server插入数据后如何获取主键？

针对批量插入主表后需要获取每条记录主键，用于后续关联插入其他表的场景，推荐以下两种实用方案：

方案一：临时表 + INSERT...OUTPUT 捕获主键 #

这是最常用的方案，既能保留SqlBulkCopy的批量插入性能，又能精准捕获每条记录的自增主键：

步骤分解 #

1. 给原DataTable添加临时唯一标识列
   在要插入的主表DataTable中新增TempId列（类型用Guid，确保每行唯一），用于后续关联返回的主键与原数据行：

   // 给主表DataTable添加临时标识列
   dataTable.Columns.Add("TempId", typeof(Guid));
   foreach (DataRow row in dataTable.Rows)
   {
       row["TempId"] = Guid.NewGuid();
   }
   

2. 批量插入到临时表
   创建与主表结构一致的本地临时表（加上TempId列），用SqlBulkCopy把数据批量导入：

   -- 在当前连接中创建临时表（结构匹配主表，排除自增主键列，新增TempId）
   CREATE TABLE #TempMainTable (
       TempId UNIQUEIDENTIFIER,
       Col1 VARCHAR(100),
       Col2 INT,
       -- 其他主表列
   )
   

   using (var bulkCopy = new SqlBulkCopy(connection))
   {
       bulkCopy.DestinationTableName = "#TempMainTable";
       // 映射所有列（包括TempId）
       foreach (DataColumn col in dataTable.Columns)
       {
           bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
       }
       bulkCopy.WriteToServer(dataTable);
   }
   

3. 插入主表并捕获主键映射
   用INSERT...OUTPUT语句将临时表数据插入正式主表，同时返回生成的主键和对应的TempId，建立映射关系：

   var primaryKeyMap = new Dictionary<Guid, int>(); // 假设主键为int类型
   using (var cmd = new SqlCommand(@"
       INSERT INTO MainTable (Col1, Col2, ...)
       OUTPUT inserted.MainId, inserted.TempId
       SELECT Col1, Col2, ..., TempId FROM #TempMainTable", connection))
   {
       using (var reader = cmd.ExecuteReader())
       {
           while (reader.Read())
           {
               int mainId = reader.GetInt32(0);
               Guid tempId = reader.GetGuid(1);
               primaryKeyMap[tempId] = mainId;
           }
       }
   }
   

4. 将主键映射回原DataSet
   根据TempId把获取到的主键赋值给原DataTable的对应行，之后就可以用这个主键插入其他三张关联表：

   foreach (DataRow row in dataTable.Rows)
   {
       Guid tempId = (Guid)row["TempId"];
       row["MainId"] = primaryKeyMap[tempId]; // 原DataSet中新增MainId列存储主键
   }
   

方案二：表值参数（TVP）结合存储过程 #

如果不想依赖临时表，可以用SQL Server的表值参数，在存储过程中完成插入并返回主键：

步骤分解 #

1. 创建表值类型

   CREATE TYPE MainTableTVP AS TABLE (
       TempId UNIQUEIDENTIFIER,
       Col1 VARCHAR(100),
       Col2 INT,
       -- 其他主表列
   )
   

2. 编写存储过程

   CREATE PROCEDURE InsertMainTableWithPK
       @tvp MainTableTVP READONLY
   AS
   BEGIN
       SET NOCOUNT ON;
       INSERT INTO MainTable (Col1, Col2, ...)
       OUTPUT inserted.MainId, inserted.TempId
       SELECT Col1, Col2, ..., TempId FROM @tvp;
   END
   

3. C#中调用存储过程并获取主键

   var primaryKeyMap = new Dictionary<Guid, int>();
   using (var cmd = new SqlCommand("InsertMainTableWithPK", connection))
   {
       cmd.CommandType = CommandType.StoredProcedure;
       var tvpParam = cmd.Parameters.AddWithValue("@tvp", dataTable);
       tvpParam.SqlDbType = SqlDbType.Structured;
       tvpParam.TypeName = "MainTableTVP";
   
       using (var reader = cmd.ExecuteReader())
       {
           while (reader.Read())
           {
               int mainId = reader.GetInt32(0);
               Guid tempId = reader.GetGuid(1);
               primaryKeyMap[tempId] = mainId;
           }
       }
   }
   

   后续的主键映射和关联表插入步骤与方案一一致。

注意事项 #

• 如果你的主键是GUID类型（而非自增INT），可以提前在DataTable中生成唯一GUID，直接作为主键插入，无需后续捕获，流程会更简单。
• 本地临时表（#开头）是会话级别的，不会与其他会话冲突，用完可自动销毁，无需手动删除。
• 两种方案的性能都远优于循环插入，适合大数据量场景。

内容的提问来源于stack exchange，提问作者Sri Ram Kumar