CODEWITHSUNDEEP
sqlsql-servert-sqlvarchar
Thomas A Mathew
Thomas A Mathew

Reputation: 159

SQL Server get Identity Number and assign to another column value

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

Upvotes: 1

Views: 1035

Answers (2)

eshirvana
eshirvana

Reputation: 24633

@@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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I would suggest created a generated column:

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

This is calculated when the column is referenced (unless the value is persisted).

To be honest, I'm not sure if you really need such a column. The identity column should be sufficient.

Upvotes: 3

Related Questions