How to get newly inserted Ids from identity primary key column when using SqlBulkCopy

Question

I'm using C# and .NET Core 6. I want to bulk insert about 100 rows at once into database and get back their Ids from BigInt identity column.

I have tried lot of different variants, but still do not have the working solution. When I preview table variable, the Id column has DbNull value and not the newly inserted Id.

How to get Ids of newly inserted rows?

What I have:

SQL Server table:

CREATE TABLE dbo.ExamResult 
(
    Id bigint IDENTITY(1, 1) NOT NULL,
    Caption nvarchar(1024) NULL,
    SortOrder int NOT NULL,

    CONSTRAINT PK_ExmRslt PRIMARY KEY CLUSTERED (Id)
) ON [PRIMARY]
GO

C# code:

private static void BulkCopy(IEnumerable<ExamResultDb> examResults, SqlConnection connection)
{
    using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, null))
    {
        var table = CreateDataTable(examResults);

        bulkCopy.DestinationTableName = "ExamResult";
        bulkCopy.EnableStreaming = true;

        foreach (var item in table.Columns.Cast<DataColumn>())
        {
            if (item.ColumnName != "Id")
            {
                bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
            }
        }

        using (var reader = new DataTableReader(table))
        {
            bulkCopy.WriteToServer(reader);
        }
    }
}

private static DataTable CreateDataTable(IEnumerable<ExamResultDb> examResults)
{
    var table = new DataTable("ExamResult");
    table.Columns.AddRange(new[]
                           {
                               new DataColumn("Id", typeof(long)),
                               new DataColumn("Caption", typeof(string)),
                               new DataColumn("SortOrder", typeof(int))
                           });

    ////table.Columns[0].AutoIncrement = true;

    ////table.PrimaryKey = new[] { table.Columns[0] };

    foreach (var examResult in examResults)
    {
        var row = table.NewRow();

        row["Caption"] = examResult.Caption;
        row["SortOrder"] = examResult.SortOrder;

        table.Rows.Add(row);
    }

    return table;
}

The general practice is to use BCP/Bulk Insert/SQLBulkCopy into a dedicated staging table to make resolving this and many other possible issues much, *much* easier. If you are bulk inserting into a production OLTP table then this can be difficult (and there are other potential problems you may not have thought of yet). As it stands now, it would help to know more about what other concurrent usage there might be of your ExamResult table. — RBarryYoung, Jul 09 '22 at 13:30
[This answer](https://stackoverflow.com/questions/25207790/how-to-get-id-of-records-inserted-into-a-table-by-using-sqlbulkcopy) says that you can't and also gives some workarounds. However, it is also 8 years old so I'm not certain if it is still correct or fair to call it a duplicate. — RBarryYoung, Jul 09 '22 at 13:35

score 1 · Accepted Answer · answered Jul 10 '22 at 03:38

You need an OUTPUT clause on your insert, but Bulk Copy does not allow this kind of customization.

While nowhere near as neat, you could do this by using a Table Valued Parameter and a custom INSERT statement. TVPs use the bulk copy mechanism, so this should still be pretty fast, although there will be two inserts: one to the TVP and one to the real table.

First create a table type

CREATE TYPE dbo.Type_ExamResult AS TABLE
(
    Caption nvarchar(1024) NULL,
    SortOrder int NOT NULL
);

This function will iterate the rows as SqlDatRecord

private static IEnumerable<SqlDataRecord> AsExamResultTVP(this IEnumerable<ExamResultDb> examResults)
{
    // fine to reuse object, see https://stackoverflow.com/a/47640131/14868997
    var record = new SqlDataRecord(
        new SqlMetaData("Caption", SqlDbType.NVarChar, 1024),
        new SqlMetaData("SortOrder", SqlDbType.Int)
    );

    foreach (var examResult in examResults)
    {
        record.SetString(0, examResult.Caption);
        record.SetInt32(0, examResult.SortOrder);
        yield return record;  // looks weird, see above link
    }
}

Finally insert using OUTPUT

private static void BulkCopy(IEnumerable<ExamResultDb> examResults, SqlConnection connection)
{
    const string query = @"
INSERT dbo.ExamResult (Caption, SortOrder)
OUTPUT Id, SortOrder
SELECT t.Caption, t.SortOrder
FROM @tvp t;
";

    var dict = examResults.ToDictionary(er => er.SortOrder);
    using (var comm = new SqlCommand(query, connection))
    {
        comm.Parameters.Add(new SqlParameter("@tmp", SqlDbType.Structured)
        {
            TypeName = "dbo.Type_ExamResult",
            Value = examResults.AsExamResultTVP(),
        });
        
        using (var reader = comm.ExecuteReader())
        {
            while(reader.Read())
                dict[(int)reader["SortOrder"]].Id = (int)reader["Id"];
        }
    }
}

Note that the above code assumes that SortOrder is a natural key within the dataset to be inserted. If it is not then you will need to add one, and if you are not inserting that column then you need a rather more complex MERGE statement to be able to access that column in OUTPUT, something like this:

MERGE dbo.ExamResult er
USING @tvp t
ON 1 = 0 -- never match 
WHEN NOT MATCHED THEN
  INSERT (Caption, SortOrder)
  VALUES (t.Caption, t.SortOrder)
OUTPUT er.Id, t.NewIdColumn;

How to get newly inserted Ids from identity primary key column when using SqlBulkCopy

1 Answers1

Linked