SQL INSERT语句如何生成从目标列最大值+1起始的唯一序列值

可以通过ROW_NUMBER函数叠加目标表当前最大值的方式实现需求，修改后的INSERT语句如下：

INSERT INTO PS_JPM_JP_ITEMS  (JPM_PROFILE_ID, JPM_CAT_TYPE, JPM_CAT_ITEM_ID, EFFDT, EFF_STATUS, JPM_ITEM_KEY_ID) 
    SELECT 
        HL.JPM_PROFILE_ID, HL.EDLVLACHV, 
        [Map to JPM_CAT_ITEM_ID], EFFDT, EFF_STATUS, 
        -- 用ROW_NUMBER生成的序号叠加现有最大值，实现从MAX+1开始递增
        ROW_NUMBER() OVER (ORDER BY HL.JPM_PROFILE_ID) + (SELECT ISNULL(MAX(JPM_ITEM_KEY_ID), 0) FROM PS_JPM_JP_ITEMS)
    FROM 
        #TempHighLevel AS HL
    LEFT OUTER JOIN
        #TempSchoolingFromTalent AS S ON HL.GHS_OC_ID = S.GHS_OC_ID 
                                      AND HL.[Map to JPM_CAT_ITEM_ID] COLLATE SQL_Latin1_General_CP1_CI_AS = S.JPM_CAT_ITEM_ID
    WHERE
        S.GHS_OC_ID IS NULL
        AND HL.JPM_PROFILE_ID IS NOT NULL

实现逻辑 #

• 先通过子查询SELECT ISNULL(MAX(JPM_ITEM_KEY_ID), 0) FROM PS_JPM_JP_ITEMS获取该列现有最大值，你当前最大值为1135814时，这一步返回结果就是1135814；如果目标表为空，ISNULL会兜底返回0，保证第一行值为1
• ROW_NUMBER生成从1开始的连续序号，和上面的最大值相加后，第一行结果为1135814+1=1135815，后续逐行加1，完全符合你的要求
• 运算结果和decimal(12,0)类型兼容，不需要额外做类型转换

注意事项 #

如果存在多个会话同时向PS_JPM_JP_ITEMS插入数据的并发场景，该写法可能出现值冲突，建议：

• 单批次插入时放在独立事务中执行，锁定表避免其他写入
• 若使用SQL Server 2012+、Oracle等支持序列的数据库，可创建专用序列生成该列值，并发安全性更高

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