0

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;
jnm2
  • 7,960
  • 5
  • 61
  • 99

1 Answers1

1

Using merge..output to get mapping between source.id and target.id

Community
  • 1
  • 1
Sonam
  • 3,406
  • 1
  • 12
  • 24
  • How would that allow me to retrieve the identity keys from the main table? – jnm2 Jul 19 '13 at 15:28
  • I apologize, I answered to this question based on the heading alone. If you are working on SQL server 2008 then you can use merge and Output to get the mapping between the source id and target id. I'm editing my answer to include the reference link for the same. – Sonam Jul 19 '13 at 15:47
  • I think SCOPE_IDENTITY(SQL 2012) would help you out. here is a link: http://msdn.microsoft.com/en-us/library/ms190315.aspx – Sonam Jul 19 '13 at 18:16