1

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 ?

Hemant Kumar
  • 4,593
  • 9
  • 56
  • 95
  • just get the IDENTITY the same way you would if you inserted 1 record... the number of records is irrelevant? – Seabizkit Oct 27 '16 at 08:20
  • select them after they are inserted, you would need to include a bulk inserted identifier... so add column batchId or something and then re-select all records with the same batchid. – Seabizkit Oct 27 '16 at 08:23
  • just out of interest how many records are we talking about. (Ball park) – Seabizkit Oct 27 '16 at 08:23

4 Answers4

3

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

Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • Hi how can I add/map the `bulk.ColumnMappings.Add("CustomerID", ColumnMappingDirectionType.Output);` to a different table like for e.g. a `customerOrder table` – Transformer May 29 '19 at 00:46
  • 1
    You cannot map it to a different table. You can return it however and use the value for the next bulk operations. Here is a Fiddle that shows how to return the value: https://dotnetfiddle.net/g5pSS1 – Jonathan Magnan May 29 '19 at 11:16
  • wow, this is much simpler easier lib to use. I got stuck, _was not able to get the output ids_ https://dotnetfiddle.net/yMfhPD , how can I get the output id's cached/list? 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 – Transformer May 29 '19 at 14:17
0

I've used a solution similar to this one from Marc Gravell in that it is useful to first import into a temp table.

I've also used the MERGE and OUTPUT as described by Jamie Thomson on this post to track data I have inserted into my temp table to match it with the id generated by the IDENTITY column of the table I want to insert into.

This is particularly useful when you need to use that ID as a foreign key reference to other tables you are populating.

Community
  • 1
  • 1
Mr Moose
  • 5,946
  • 7
  • 34
  • 69
0

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
Ahmed Saeed
  • 831
  • 7
  • 12
-3
-- dummy schema
 CREATE TABLE TMP (data varchar(max))
 CREATE TABLE [Table1] (id int not null identity(1,1), data varchar(max))
 CREATE TABLE [Table2] (id int not null identity(1,1), id1 int not null, data varchar(max))

 -- imagine this is the SqlBulkCopy
 INSERT TMP VALUES('abc')
 INSERT TMP VALUES('def')
 INSERT TMP VALUES('ghi')

 -- now push into the real tables
 INSERT [Table1]
 OUTPUT INSERTED.id, INSERTED.data INTO [Table2](id1,data)
 SELECT data FROM TMP
An88
  • 11
  • 1
  • 3
    @Ann88, that is directly copied from Marc Gravell's answer I linked in my answer. Please ensure you note the source of your answer if you are going to copy it. – Mr Moose Oct 27 '16 at 08:21