如何在含identity列pkid的表插入记录时生成VIP000000+pkid格式的cust_num

针对你的tbl01表，要在插入时自动生成VIP+7位补零pkid格式的cust_num，可以采用以下几种实用方案：

方案1：创建INSTEAD OF INSERT触发器（自动处理所有插入） #

通过触发器拦截插入操作，自动生成并填充cust_num，插入时只需提供cust_name即可：

CREATE TRIGGER trg_tbl01_GenerateCustNum
ON [dbo].[tbl01]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 临时存储插入后生成的pkid
    DECLARE @InsertedPKs TABLE (pkid NUMERIC(9,0));

    -- 插入cust_name，让identity自动生成pkid并输出到临时表
    INSERT INTO [dbo].[tbl01] (cust_name)
    OUTPUT inserted.pkid INTO @InsertedPKs
    SELECT cust_name FROM inserted;

    -- 根据pkid格式化生成cust_num
    UPDATE t
    SET cust_num = 'VIP' + RIGHT('0000000' + CAST(t.pkid AS VARCHAR(7)), 7)
    FROM [dbo].[tbl01] t
    JOIN @InsertedPKs ip ON t.pkid = ip.pkid;
END;

使用示例：

-- 插入时仅需指定cust_name，cust_num会自动生成
INSERT INTO [dbo].[tbl01] (cust_name) VALUES ('Alice');

方案2：插入后手动更新（适合单次插入场景） #

如果不需要全局自动处理，可先插入记录，再用SCOPE_IDENTITY()获取刚生成的pkid并更新：

-- 插入记录（仅填写cust_name）
INSERT INTO [dbo].[tbl01] (cust_name) VALUES ('Bob');

-- 获取最新生成的pkid，格式化后更新cust_num
UPDATE [dbo].[tbl01]
SET cust_num = 'VIP' + RIGHT('0000000' + CAST(SCOPE_IDENTITY() AS VARCHAR(7)), 7)
WHERE pkid = SCOPE_IDENTITY();

方案3：批量插入时的处理 #

针对批量插入场景，可通过临时表存储插入后的pkid，再批量更新：

-- 创建临时表存储插入的pkid
DECLARE @InsertedRecords TABLE (pkid NUMERIC(9,0));

-- 批量插入记录，输出pkid到临时表
INSERT INTO [dbo].[tbl01] (cust_name)
OUTPUT inserted.pkid INTO @InsertedRecords
VALUES ('Charlie'), ('David');

-- 批量生成并更新cust_num
UPDATE t
SET cust_num = 'VIP' + RIGHT('0000000' + CAST(t.pkid AS VARCHAR(7)), 7)
FROM [dbo].[tbl01] t
JOIN @InsertedRecords ir ON t.pkid = ir.pkid;

关键说明 #

• RIGHT('0000000' + CAST(pkid AS VARCHAR(7)), 7)的作用是将pkid补前导零至7位，确保格式统一（如pkid=10会转为0000010，最终生成VIP0000010）。
• 若需调整位数（如8位），只需将0000000改为00000000，同时把7改为8即可。

内容的提问来源于stack exchange，提问作者user1302236