How to retrieve server generated Identity values when using SqlBulkCopy

c# identity sqlbulkcopy sql-server

Question

I am aware that if I don't mention the identification column, I may do a bulk insert into my table.SqlBulkCopyOptions.KeepIdentity as said zzzz-8 zzzz.

The identity values that the server creates should be available for me to get and save in a datatable or even a list. I read the this article, but I don't want a version field in every table; instead, I want my code to be universal. Any advice would be much appreciated. This is my code:

public void BulkInsert(DataTable dataTable, string DestinationTbl, int batchSize)
{
    // Get the DataTable 
    DataTable dtInsertRows = dataTable;

    using (SqlBulkCopy sbc = new SqlBulkCopy(sConnectStr))
    {
        sbc.DestinationTableName = DestinationTbl;

        // Number of records to be processed in one go
        sbc.BatchSize = batchSize;

        // Add your column mappings here
        foreach (DataColumn dCol in dtInsertRows.Columns)
        {
            sbc.ColumnMappings.Add(dCol.ColumnName, dCol.ColumnName);
        }

        // Finally write to server
        sbc.WriteToServer(dtInsertRows);
    }
}
1
6
5/23/2017 12:16:52 PM

Accepted Answer

I believe you cannot.

The only method (that I am aware of) for obtaining the identity field's value(s) is eitherSCOPE_IDENTITY() while inserting rows at a time; or when using theOUTPUT method for adding a whole set.

The "simplest" method would probably be to SqlBulkCopy all of the table's records, then subsequently retrieve them all back. The difficulty in correctly and rapidly retrieving such data from the server may be the issue. (For instance, having a would be quite unsightly (and sluggish).WHERE provision thatIN (guid1, guid2, .., guid999998, guid999999) =)

Since you are currently using SqlBulkCopy, I'm going to assume that performance is a problem here. Therefore, I'd advise using theOUTPUT With this method, you'll first require a staging table into which you may SqlBulkCopy your data. Then, in order to enable running several treads simultaneously, such table should include some kind of batch-identifier (GUID?). A stored procedure is required toINSERT <table> OUTPUT inserted.* SELECT the staging table once again after transferring the data to the final destination table. This would result in a 1:1 match between the returned recordset from the process and the original dataset that was used to populate the staging table, but of course you shouldn't depend on the order of the records. As a result, matching the returned Identity-fields to the original records in your application will be your next problem.

After more consideration, I'd argue that you'll need to have (or add) a "key" to your data in order to connect the produced ids to the original data in all situations, with the exception of the row-by-row & SCOPY IDENTITY() technique, which is going to be really slow =/

6
2/10/2014 10:41:28 PM

Popular Answer

The method given by Deroby may be used, except instead of returning them via aWHERE IN (guid1, etc... Based on their sequence, you match them back up to the rows that were placed into memory.

In order to match the produced Ids to the in-memory collection of rows you just entered, I would advise adding a column to the table to match the row to a SqlBulkCopy operation.

  • In the bulk copy, create a new Guid and assign this value to each row that maps to the new column.

  • Move theWritToServer the BulkCopy object's method

  • Obtain all the rows with the same key.

  • Iterate over this list, which will be in the same order as the in-memory collection of rows; this will allow you to determine the produced id for each item.

Compared to assigning a different key to each every row, this will improve performance. Therefore, after bulk inserting the data table, you might do the following: (In my example, I'll start with a list of objects and use it to build the data table before resolving the created ids to the original objects.)

List<myObject> myCollection = new List<myObject>

Guid identifierKey = Guid.NewGuid();

//Do your bulk insert where all the rows inserted have the identifierKey
//set on the new column. In this example you would create a data table based
//off the myCollection object.

//Identifier is a column specifically for matching a group of rows to a sql  
//bulk copy command
var myAddedRows = myDbContext.DatastoreRows.AsNoTracking()
            .Where(d => d.Identifier == identiferKey)
            .ToList();


 for (int i = 0; i < myAddedRows.Count ; i++)
 {
    var savedRow = myAddedRows[i];
    var inMemoryRow = myCollection[i];

    int generatedId = savedRow.Id;

   //Now you know the generatedId for the in memory object you could set a
   // a property on it to store the value

   inMemoryRow.GeneratedId = generatedId;
 }


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow