SQL Server get Identity Number and assign to another column value

Question

I am a beginner SQL Server student and need to fulfill a requirement and don't know how

For table Products I have columns ProductID, ProductName, SerialNo.

The SerialNo should start from 1001 and increment by 1 and at the same time ProductId should start as P1001, P1002 and so...

I defined

SerialNo INT Identity(1001, 1)

and don't know how to take the identity value and append it to 'P' and tried using variables and can't figure it out

BEGIN 
    DECLARE @ProductID VARCHAR(5)
    SET @ProductID = 'P' + CAST(@@IDENTITY AS VARCHAR)
    INSERT INTO Product VALUES(@ProductID,'Nokia')
    SELECT * FROM Product
END

I got

ProductID       Name        SerialNo
--------------------------------------
NULL            NOKIA       1001

Expected output is

ProductID       Name        SerialNo
-------------------------------------
P1001            NOKIA       1001

Answer 1

@@IDENTITY keeps the last insert identity in the session, so its not useful for you , you have several option:

• add a computed column :

  alter table product add ProductId as concat('P',SerialNo)  
  

• use IDENT_CURRENT : IDENT_CURRENT give you the last identity values in the table

  INSERT INTO Product VALUES(concat('P',IDENT_CURRENT('dbo.product')+1) ,'Nokia')
  SELECT * FROM Product
  

I recommend you go with computed column , however you always can reproduce the productId , not sure why you need to save it , its redundunt