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;
}

Answer 1

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;