On SQL INSERT can I use the identity column for pa

Question

CREATE TABLE Table1 :
Id int IDENTITY(1,1),
PK_Column1 nvarchar(50) Primary Key.

INSERT INTO Table1 (PK_Column1) VALUES ('Name'+Id)

Result:

Id  PK_Column1
1   Name1

Is this possible? Or do I need to manage the Id column myself for this to work?

Answer 1

From the documentation:

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.

This applies to all the other identity checkers.

You should probably write a little SP to update the record immediately after your insert if this is what you need. Given that your primary_key appears to be some unusual composite of the ID and a varchar, you would also be best reviewing your data model.

It's important to note the difference with @@IDENTITY and SCOPE_IDENTITY():

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.