Using identity value in another column
-
Hi,
I'm want to take a generated identity column in a given record, and in the same insert operation put that value in another column.
For example - I have the ID column, and I want to make insert a new record and put the ID value in the NEWID column as well.
Is there any way to accmplish this?
thank you,
roman.
-
Create the other column as a computed column. Look up computed columns in BoL.
-
Thank you for the reply.
If I create the column as a computed column, I won't be able to change it manualy afterwards I guess, but that's an ability i must have in this table.
Is there a way to change a computer column later to another value than the computed one, or use another technique?
-
No because computed columns by default are not actually stored (this can be changed). Your better option then is to use a default value. You can reference a function in your default definition if you'd like which will help do any data modification (i.e. if you want the identity value inserted as 1 to be stored as something like ABC000001 or whatever).
-
Use a trigger on the table for inserted values. In the trigger, put the identity column's value into your newID column.
Alternatively you could leave newID as null and have your logic use the ID column's value if newID is null or read the value from newID otherwise. An extra to this would be to have a computed column do this if/else for you (if you're searching it make it indexed!) or use a view (again, if you're searching on the field make the view indexed)....
The trigger's probably easiest
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply