0

I've got a self-referencing table that has a nullable, unique FK column referencing the PK for another row in the same table. This forms a linked list structure.

I've got all of my various interactions working, including insertions -- just go backward with the first item inserted being the last item in the linked list and each following insert referencing the row number prior.

However, I don't love having to do multiple inserts to handle this. Is there any way to do a multi-row insert where each row being inserted uses the @@IDENTITY of the previous row?

I wondered about something like this, but @@IDENTITY just returns null for all of the next values.

INSERT INTO LINKED_LIST (next)
SELECT @@IDENTITY
UNION ALL
SELECT @@IDENTITY
UNION ALL
SELECT @@IDENTITY

Reading the MSDN documentation, it makes sense that this doesn't work since the value is not set until after the INSERT returns, but the question is whether there is some clever way to achieve the same goal in another way?

Dan
  • 3,246
  • 1
  • 32
  • 52
  • What version of sql server do you have? 2008 - 2012 -2014 ??? – M.Ali Jul 03 '14 at 15:36
  • This needs to be supported for `2012` and `2014`. – Dan Jul 03 '14 at 15:38
  • Look at the new feature [`Sequence Number`](http://msdn.microsoft.com/en-us/library/ff878091.aspx) – M.Ali Jul 03 '14 at 15:43
  • @M.Ali If I'm reading the documentation correctly, using a `Sequence Number` would mean I can no longer control the FK constraint in the DB layer. I would rather not pawn that responsibility off on the app layer... – Dan Jul 03 '14 at 15:50
  • maybe try a trigger to update the previous_id? – Kevin Cook Jul 03 '14 at 16:40

0 Answers0