How to Insert auto-generated identity value as part of a hierarchyid field on the same record

Question

I am trying to find a way to insert a record into a table that uses the newly generated Identity value as part of it's hierarchy id. The following sql demonstrates what I am trying to do, and the closest I have managed to come. Which is to use an insert followed by an update inside of a transaction. I'm having trouble coming up with the initial hierarchy id though because there is a unique constraint on that field, and I am worried that could throw an error if 2 elements are being added to the same parent at the same time.

DECLARE @hierarchy_elements TABLE (
     id int IDENTITY (1, 1) NOT NULL ,
     element_path hierarchyid NOT NULL
)

-- Cheating here, but I need some data to append to.
INSERT INTO @hierarchy_elements(element_path)
SELECT ('/1/')
UNION ALL SELECT ('/1/2/')

-- See that we have a couple elements in the table.
SELECT id, element_path.ToString() as [path] from @hierarchy_elements

-- arbitrarily pick a parent to append to
DECLARE @parentElementId int = 2

-- grab that parent's path.
DECLARE @parentElementPath hierarchyid 
SELECT @parentElementPath = element_path FROM @hierarchy_elements WHERE id = @parentElementId

-- This is what I want to do.  Use the current id as the last part of the hierarchyid
INSERT INTO @hierarchy_elements (element_path)
VALUES(@parentElementPath.ToString() + CAST(scope_identity() AS VARCHAR(20)) + '/')

-- This works, but kind of sucks.
BEGIN TRANSACTION 

-- Insert under the parent with a known invalid id.
INSERT INTO @hierarchy_elements (element_path)
VALUES(@parentElementPath.ToString() + '-1/')

-- now update setting the last element in the hierarchyid to the id just generated.
UPDATE @hierarchy_elements 
SET element_path = @parentElementPath.ToString() + CAST(SCOPE_IDENTITY() AS VARCHAR(20)) + '/'
WHERE id = SCOPE_IDENTITY()

COMMIT TRANSACTION

-- See that id 3 would fail the unique constraint check, but id 4 is correct.
SELECT id, element_path.ToString() as [path] from @hierarchy_elements

If it is possible, I want to do use a single statement insert that will include the new Identity value in the hierarchyid field.

Answer 1

The general idea to solve the problem:

Generate ID of the row separately, remember it somewhere, then insert the generated ID as is in the ID column and use the same remembered value to compose the element_path.

If you use SQL Server 2012 and above it has a SEQUENCE feature. If you are using 2008 and below you can have a separate dedicated table with a single IDENTITY column for generating IDs.

So the structure of your main table would be different (id is no longer an IDENTITY):

DECLARE @hierarchy_elements TABLE (
     id int NOT NULL ,
     element_path hierarchyid NOT NULL
)

And you would have a separate object (SEQUENCE or a helper table) to generate unique IDs as needed.

You would have an extra explicit step of generating an ID, but you would be able to have only one INSERT into the main table without the need of UPDATE.

Second variant

You can use an AFTER INSERT TRIGGER to "hide" the explicit UPDATE statement. This variant is very close to your original approach. You still have to insert something in the element_path. That "something" would be adjusted in the trigger using the freshly generated ID by IDENTITY.