使用SqlBulkCopy批量插入数据时如何获取自增主键（Identity列）的新插入ID

我明白你的痛点——SqlBulkCopy虽然高效，但默认不会把生成的自增ID回写到你的DataTable或实体对象里。要解决这个问题，我们需要换个思路：先把数据批量写入临时载体（临时表或表值参数），再通过INSERT...OUTPUT语句将数据插入正式表并捕获生成的ID。

下面给你两种针对.NET Core 6和SQL Server的可行实现方案：

方案一：临时表 + SqlBulkCopy + INSERT...OUTPUT #

这种方法用临时表作为中间过渡，先批量导入数据，再插入正式表时获取自增ID，还能通过临时标识列关联原数据和生成的ID。

步骤1：修改DataTable，添加临时标识列 #

给DataTable新增一个临时唯一标识列，用来后续匹配原实体和插入后的ID：

private static DataTable CreateDataTable(IEnumerable<ExamResultDb> examResults)
{
    var table = new DataTable("ExamResult_Temp");
    table.Columns.AddRange(new[] {
        new DataColumn("TempId", typeof(Guid)), // 临时唯一ID，用于关联原对象
        new DataColumn("Caption", typeof(string)),
        new DataColumn("SortOrder", typeof(int))
    });

    foreach (var examResult in examResults)
    {
        var row = table.NewRow();
        row["TempId"] = Guid.NewGuid();
        row["Caption"] = examResult.Caption;
        row["SortOrder"] = examResult.SortOrder;
        // 把TempId存到原实体，方便后续关联
        examResult.TempId = (Guid)row["TempId"];
        table.Rows.Add(row);
    }
    return table;
}

步骤2：批量插入临时表，再写入正式表并获取ID #

修改批量插入逻辑，完成临时表导入、正式表写入和ID回写：

private static void BulkCopyAndGetIds(IEnumerable<ExamResultDb> examResults, SqlConnection connection)
{
    // 1. 创建临时表
    using (var createTempTableCmd = new SqlCommand(@"
        CREATE TABLE #TempExamResult (
            TempId UNIQUEIDENTIFIER NOT NULL,
            Caption NVARCHAR(1024) NULL,
            SortOrder INT NOT NULL
        )", connection))
    {
        createTempTableCmd.ExecuteNonQuery();
    }

    // 2. 批量插入数据到临时表
    using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, null))
    {
        var table = CreateDataTable(examResults);
        bulkCopy.DestinationTableName = "#TempExamResult";
        bulkCopy.EnableStreaming = true;
        foreach (var item in table.Columns.Cast<DataColumn>())
        {
            bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
        }
        using (var reader = new DataTableReader(table))
        {
            bulkCopy.WriteToServer(reader);
        }
    }

    // 3. 插入正式表并捕获生成的ID
    using (var insertCmd = new SqlCommand(@"
        INSERT INTO dbo.ExamResult (Caption, SortOrder)
        OUTPUT inserted.Id, source.TempId
        SELECT Caption, SortOrder FROM #TempExamResult source", connection))
    {
        using (var reader = insertCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var generatedId = reader.GetInt64(0);
                var tempId = reader.GetGuid(1);
                // 把ID赋值回对应的原实体
                var matchingResult = examResults.First(r => r.TempId == tempId);
                matchingResult.Id = generatedId;
            }
        }
    }

    // 临时表会在连接关闭后自动删除，这里手动清理也可以
    using (var dropTempTableCmd = new SqlCommand("DROP TABLE #TempExamResult", connection))
    {
        dropTempTableCmd.ExecuteNonQuery();
    }
}

补充：实体类新增TempId属性 #

记得给ExamResultDb添加临时标识属性：

public class ExamResultDb
{
    public long Id { get; set; }
    public string Caption { get; set; }
    public int SortOrder { get; set; }
    public Guid TempId { get; set; } // 新增临时关联字段
}

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

如果希望逻辑更集中在数据库端，或者场景更复杂，可以用表值参数结合存储过程实现。

步骤1：创建SQL Server用户定义表类型 #

CREATE TYPE dbo.ExamResultType AS TABLE (
    TempId UNIQUEIDENTIFIER NOT NULL,
    Caption NVARCHAR(1024) NULL,
    SortOrder INT NOT NULL
)
GO

步骤2：创建存储过程，插入数据并返回ID #

CREATE PROCEDURE dbo.InsertExamResults
    @Results dbo.ExamResultType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.ExamResult (Caption, SortOrder)
    OUTPUT inserted.Id, source.TempId
    SELECT Caption, SortOrder FROM @Results source;
END
GO

步骤3：C#代码调用存储过程 #

private static void BulkInsertWithTVP(IEnumerable<ExamResultDb> examResults, SqlConnection connection)
{
    // 创建带TempId的DataTable（和方案一的CreateDataTable逻辑一致）
    var table = CreateDataTable(examResults);

    using (var cmd = new SqlCommand("dbo.InsertExamResults", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // 添加表值参数
        var tvpParam = new SqlParameter("@Results", SqlDbType.Structured)
        {
            TypeName = "dbo.ExamResultType",
            Value = table
        };
        cmd.Parameters.Add(tvpParam);

        // 读取返回的ID并赋值回原实体
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var generatedId = reader.GetInt64(0);
                var tempId = reader.GetGuid(1);
                var matchingResult = examResults.First(r => r.TempId == tempId);
                matchingResult.Id = generatedId;
            }
        }
    }
}

为什么原代码无法获取ID？ #

SqlBulkCopy的核心设计是高效批量写入，它只负责把数据推送到数据库，没有内置机制将数据库生成的自增ID回传给客户端的DataTable。因此必须通过OUTPUT子句主动捕获这些ID，再关联回你的实体对象。

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