Geek Answers Handbook

How to get server-generated Identity values ​​when using SqlBulkCopy

I know that I can do bulk insertion into a table with an identity column without specifying SqlBulkCopyOptions.KeepIdentity , as mentioned here .

What I would like to do is get the identification values ​​that the server generates and put them in my datatable or even a list. I saw this post, but I want my code to be generic, and I cannot have a version column in all my tables. Any suggestions are greatly appreciated. Here 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); } }
+6
source share
2 answers

AFAIK, you cannot.

The only way (which I know) to get the values ​​of the identification field is to use either SCOPE_IDENTITY() when inserting row by row; or using the OUTPUT approach when inserting the entire set.

The “simplest” approach would probably be that you would SqlBulkCopy the records in the table and then return them again. The problem may be that it would be difficult to correctly (and quickly) retrieve these lines from the server again. (for example, it would be pretty ugly (and slow) to have a WHERE with IN (guid1, guid2, .., guid999998, guid999999) =)

I assume that performance is a problem here since you are already using SqlBulkCopy, so I suggest moving on to the OUTPUT approach, in which case you will first need a staging table for SqlBulkCopy of your records. the table should include some kind of batch identifier (GUID?), allowing several passes to work side by side. You will need a stored procedure for the INSERT <table> OUTPUT inserted.* SELECT data from the staging table to the actual destination table, and also clear the staging table again. The returend recordset from the specified procedure will then correspond 1: 1 to the original data set, which is responsible for filling the intermediate table, but, of course, you should not rely on its order. In other words: your next task is to map the returned Identity fields to the source records in your application.

Thinking about this, I would say that in all cases - except for the line by line approach and SCOPY_IDENTITY (), which will be slow with the dog - you will need (or add) a “key” to your data in order to associate the generated identifier with source data = /

+6
source

You can do a similar approach described above deroby, but instead of returning them back through WHERE IN (guid1, etc... You match them with the lines inserted into memory, depending on their order.

So, I would suggest adding a column to the table to map the row to the SqlBulkCopy transaction, and then do the following to combine the generated identifiers back into the memory collection of the rows just inserted.

  • Create a new Guid and set this value in all rows of the bulk copy match to a new column

  • Run the WritToServer method of the WritToServer object

  • Extract all rows that have the same key

  • Go through this list, which will be in the order in which they were added, they will be in the same order as in the memory collection of strings, so you will know the generated identifier for each element.

This will give you better performance than providing each individual line with a unique key. Therefore, after you insert the data array into the data table, you can do something like this (in my example, I will have a list of objects from which I will create the data table and then return them back).

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

More articles:


All Articles