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.
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:
inserted, filter where SID is NULLSID = IDIf 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.