SQL Server 获取标识号并分配给另一个列值

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

我定义了

SerialNo INT Identity(1001, 1)

并且不知道如何获取标识值并将其附加到P"并尝试使用变量但无法弄清楚

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

我得到了

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

预期输出是

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

推荐答案

@@IDENTITY 保留会话中最后一个插入标识，所以它对你没有用，你有几个选择:

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

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

我建议你使用计算列，但是你总是可以重现 productId ，不知道为什么你需要保存它，它是冗余的

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

这篇关于SQL Server 获取标识号并分配给另一个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！