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 ?

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);

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


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why