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 DataTable
.
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
Try this
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