I have to get the IDENTITY values from a table after SQLBULKCOPY to the same table. The volume of data could be thousands of records.
Can someone help me out on this ?
Disclaimer: I'm the owner of the project Bulk Operations
In short, this project overcomes SqlBulkCopy limitations by adding MUST-HAVE features like outputting inserted identity value.
Under the hood, it uses SqlBulkCopy and a similar method as @Mr Moose answer.
var bulk = new BulkOperation(connection) // Output Identity Value bulk.ColumnMappings.Add("CustomerID", ColumnMappingDirectionType.Output); // Map Column bulk.ColumnMappings.Add("Code"); bulk.ColumnMappings.Add("Name"); bulk.ColumnMappings.Add("Email"); bulk.BulkInsert(dt);
EDIT: Answer comment
can I simply get a IList or simply , I see its saved back in the customers table, but there is no variable where I can get a hold of it, can you please help with that. So, I an insert in Orders.CustomerID table
It depends, you can keep a reference to the Customer DataRow named
CustomerRef in the Order
So once you merged your customer, you are able to populate easily a column
CustomerID from the column
CustomerRef in your Order DataTable.
Here is an example of what I'm trying to say: https://dotnetfiddle.net/Hw5rf3
CREATE TABLE #temp ( DataRow varchar(max) ) BULK INSERT #Temp FROM 'C:\tt.txt' ALTER TABLE #temp ADD id INT IDENTITY(1,1) NOT NULL SELECT * FROM #temp