How to get the Identity values in SQL BULK COPY?

c# sqlbulkcopy sql-server

Question

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 ?

1
1
10/27/2016 8:05:25 AM

Expert Answer

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

2
5/30/2019 12:05:15 PM

Popular Answer

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


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