What is best practice for updating server generated entity IDs after a bulk insert?

entity identity-column orm sqlbulkcopy sql-server

Question

There are just under a million nodes in an object graph, mostly disconnected, that need to be imported in bulk.

The keys are all server-generated. There are several levels of relations, so after each batch is inserted the correct identity keys need to be retrieved to use as the foreign key values in the next batch.

What is the simplest way to do this? Is there an output from BULK INSERT?

Or could it work well to adjust the current incremental ID value on the table to make room for the number of rows we're inserting, assign IDs on the client side from these numbers, and bulk copy with identity insert? E.g. if the next incremental ID would be 120,001 and there are 600,000 rows to insert, set the next ID to 720,001 and manually use numbers from 120,001 to 720,000 as IDs before any inserts?

declare @lastID int, @newLastID int;

begin tran
    set @lastID = ident_current('dbo.Strains');
    set @newLastID = @lastID + 1000;

    dbcc checkident('dbo.Strains', reseed, @newLastID);
commit

select @lastID + 1;

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