SQL insert ID from IDENTITY ID of the row being inserted

Question

I would like to know, if there is a direct way to insert ID (generated at ID column with IDENTITY(1,1)) to another columns. In another words, I am looking for SCOPE_IDENTITY() I could get at the time of inserting, not after the INSERT is commited.

I have a table, where there is a column with secondary ID (SID), which references rows from the same table and in some special cases it references itself. The only way I know to do that is to do the INSERT and consequently UPDATE SID in those cases. Simplified example:

DECLARE @ID INT
INSERT INTO Table (SID) VALUES (NULL);
SELECT @ID = SCOPE_IDENTITY();
UPDATE Table SET SID = ID WHERE ID = @ID;

There are some glitches, i.e. due to the fact that the row may or may not reference itself, etc.

Answer 1

You can do this with an AFTER INSERT trigger. In case of self-reference, leave the column NULL and have the trigger set the column equal to the IDENTITY column.

In pseudo:

• Join the table with inserted, filter where SID is NULL
• For those rows, update the table and set SID = ID

---

If it is not possible to use the NULL value, in cases where it should be possible to have no reference at all, you can use another stub value. E.g. -1 if the IDs will always be positive. In that case, apply the above way of working and substitute NULL with -1.