Reputation: 159
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
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
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