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

2021-09-10 00:00:00 sql tsql sql-server varchar

我是 SQL Server 初学者，需要满足要求但不知道如何

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

对于表 Products 我有列 ProductID、ProductName、SerialNo.

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

SerialNo 应该从 1001 开始并加 1，同时 ProductId 应该从 P1001, P1002<开始/code> 等等...

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)

  • 使用 IDENT_CURRENT :IDENT_CURRENT 为您提供表中的最后一个标识值

  • 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

    • 我建议你使用计算列，但是你总是可以重现 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

    相关文章