Можно ли вернуть идентификаторы PrimaryKey после SQL BulkCopy?

В этом случае я бы использовал SqlBulkCopy для вставки в промежуточную таблицу (т.е. ту, которая выглядит как данные, которые я хочу импортировать, но не является частью основных транзакционных таблиц), а затем в БД в INSERT/SELECT для перемещения данных в первую реальную таблицу.

Теперь у меня есть два варианта в зависимости от версии сервера; Я мог бы выполнить вторую операцию INSERT/SELECT для второй реальной таблицы или использовать предложение INSERT/OUTPUT для выполнения второй вставки, используя строки идентификаторов из таблицы.

Например:

     -- dummy schema
     CREATE TABLE TMP (data varchar(max))
     CREATE TABLE [Table1] (id int not null identity(1,1), data varchar(max))
     CREATE TABLE [Table2] (id int not null identity(1,1), id1 int not null, data varchar(max))

     -- imagine this is the SqlBulkCopy
     INSERT TMP VALUES('abc')
     INSERT TMP VALUES('def')
     INSERT TMP VALUES('ghi')

     -- now push into the real tables
     INSERT [Table1]
     OUTPUT INSERTED.id, INSERTED.data INTO [Table2](id1,data)
     SELECT data FROM TMP

Если ваше приложение позволяет это, вы можете добавить еще один столбец, в котором вы храните идентификатор массовой вставки (например, guid). Вы бы явно установили этот идентификатор.

Затем после массовой вставки вы просто выбираете строки с этим идентификатором.

У меня была та же проблема, когда мне приходилось возвращать идентификаторы строк, вставленных с помощью SqlBulkCopy. Мой столбец идентификаторов был столбцом идентификаторов.

Решение:

Я вставил более 500 строк с массовым копированием, а затем выбрал их обратно с помощью следующего запроса:

SELECT TOP InsertedRowCount * 
FROM   MyTable 
ORDER BY ID DESC

Этот запрос возвращает строки, которые я только что вставил, с их идентификаторами. В моем случае у меня был еще один уникальный столбец. Поэтому я выбрал этот столбец и идентификатор. Затем сопоставил их с IDictionary следующим образом:

 IDictionary<string, int> mymap = new Dictionary<string, int>()
 mymap[Name] = ID

Надеюсь это поможет.

Мой подход аналогичен тому, что описал RiceRiceBaby, за исключением одного важного добавления: вызов для получения Max(Id) должен быть частью транзакции вместе с вызовом SqlBulkCopy.WriteToServer. В противном случае кто-то другой может вставить во время вашей транзакции, и это сделает ваш идентификатор неверным. Вот мой код:

public static void BulkInsert<T>(List<ColumnInfo> columnInfo, List<T> data, string 
destinationTableName, SqlConnection conn = null, string idColumn = "Id")
    {
        NLogger logger = new NLogger();

        var closeConn = false;


        if (conn == null)
        {
            closeConn = true;
            conn = new SqlConnection(_connectionString);
            conn.Open();
        }

        SqlTransaction tran = 
    conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

        try
        {
            var options = SqlBulkCopyOptions.KeepIdentity;
            var sbc = new SqlBulkCopy(conn, options, tran);

            var command = new SqlCommand(
                    $"SELECT Max({idColumn}) from {destinationTableName};", conn, 
           tran);
            var id = command.ExecuteScalar();

            int maxId = 0;

            if (id != null && id != DBNull.Value)
            {
                maxId = Convert.ToInt32(id);
            }

            data.ForEach(d =>
                {
                    maxId++;
                    d.GetType().GetProperty(idColumn).SetValue(d, maxId);
                });

            var dt = ConvertToDataTable(columnInfo, data);

            sbc.DestinationTableName = destinationTableName;

            foreach (System.Data.DataColumn dc in dt.Columns)
            {
                sbc.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
            }

            sbc.WriteToServer(dt);

            tran.Commit();

            if(closeConn)
            {
                conn.Close();
                conn = null;
            }
        }
        catch (Exception ex)
        {
            tran.Rollback();
            logger.Write(LogLevel.Error, $@"An error occurred while performing a bulk 
insert into table {destinationTableName}. The entire
                                           transaction has been rolled back. 

{ex.ToString()}");
            throw ex;
        }
    }

В зависимости от ваших потребностей и того, насколько вы контролируете таблицы, вы можете рассмотреть возможность использования UNIQUEIDENTIFIER (Guids) вместо ваших первичных ключей IDENTITY. Это перемещает управление ключами за пределы базы данных в ваше приложение. В этом подходе есть несколько серьезных недостатков, поэтому он может не соответствовать вашим потребностям. Но, возможно, стоит задуматься. Если вы точно знаете, что будете загружать много данных в свои таблицы с помощью массовой вставки, часто очень удобно, чтобы эти ключи управлялись в вашей объектной модели, а не ваше приложение полагалось на базу данных, чтобы вернуть вам данные.

Вы также можете использовать гибридный подход с промежуточными таблицами, как было предложено ранее. Получите данные в эти таблицы, используя GUID для отношений, а затем с помощью операторов SQL вы можете получить целые внешние ключи в порядке и загрузить данные в свои производственные таблицы.

Я буду:

1. Включить вставку удостоверений в таблицу

2. Возьмите идентификатор последней строки таблицы

3. Цикл из (int i = Id; i < datable.rows.count+1; i++)

4. В цикле присвойте свойству Id вашего объекта данных значение i+1.

5. Запустите массовую вставку SQL с включенным сохранением удостоверения.

6. Отключить вставку удостоверения личности

Я думаю, что это самый безопасный способ получить ваши идентификаторы при массовой вставке SQL, потому что это предотвратит несовпадающие идентификаторы, которые могут быть вызваны приложением, выполняемым в другом потоке.

Отказ от ответственности: я являюсь владельцем проекта C# Bulk Operations

Библиотека преодолевает ограничения SqlBulkCopy и добавляет гибкие функции, такие как вывод вставленного значения идентификатора.

За кодом он точно такой же, как принятый ответ, но намного проще в использовании.

var bulk = new BulkOperation(connection);

// Output Identity
bulk.ColumnMappings.Add("ProductID", ColumnMappingDirectionType.Output);
// ... Column Mappings...

bulk.BulkInsert(dt);