3

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.

Improve this question
5
  • your question is unclear. What is wrong with the INSERT/UPDATE scenario? Commented Oct 23, 2017 at 9:10
  • It works, but makes the script longer with a risk of error, especially, because the example is simplified and there are cases to be distinguished. Commented Oct 23, 2017 at 9:14
  • One way to reduce risk of errors, is to go with Sequences. Look it up. Commented Oct 23, 2017 at 9:22
  • @Oak_3260548 wrap it in single transaction if you are aware of errors. Commented Oct 23, 2017 at 9:30
  • I agree, it is wrapped in a single transaction already. I have to use insert+update in this case due to the fact, that one "SID" column may have valid NULL values. In other cases AFTER INSERT trigger seems to be a fine solution. Commented Oct 23, 2017 at 10:40

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Since there are no other answers, I consider it the only solution to avoid the procedure described in my question, so I marked it as an answer. I can use it in some cases, though not in this particular case, because one of the "SID" columns (there are more then 1) may be NULL (have no refference).
@Oak_3260548 That is tough, I don't really see any other way. Perhaps you could use a stub value other than NULL for self-referencing? E.g. the minimum value of INT (-2,147,483,648)?
Yes, that came to my mind right after I posted the comment. I could simply use i.e. (-1) value, since my IDs are obviously always positive... It would be great if you add this case into your answer for future references for other users.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.