問題描述
我是 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
這篇關于SQL Server 獲取標識號并分配給另一個列值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！