SQL Server基于指定起始值逐行生成连续ID用于INSERT查询

实现前提 #

• 无法修改目标表结构配置标准自增序列，只能通过SQL语句生成待插入数据的ID值
• 业务场景为每日单次执行插入操作，无需考虑并发插入导致的ID冲突问题

基础ID计算逻辑 #

首先查询目标表现有最大ID：

SELECT MAX(ID)
FROM 你的目标表名;

示例中该查询返回结果为16684，新插入数据的ID起始值即为最大ID加1，对应查询：

SELECT MAX(ID) + 1
FROM 你的目标表名;

示例返回起始ID为16685。

批量插入时连续ID生成方案 #

需求是在INSERT INTO ... SELECT语句中，以上述起始值为起点，给SELECT返回的每一行逐行生成连续递增ID，最终插入效果如下：

16685, Data, Data, Data
16686, Data, Data, Data
16687, Data, Data, Data
16688, Data, Data, Data
16689, Data, Data, Data
...

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

直接用ROW_NUMBER()窗口函数给待插入结果集生成连续行号，和最大ID相加即可得到连续递增ID，写法如下：

INSERT INTO 你的目标表名 (ID, 其他列1, 其他列2, 其他列3)
SELECT
    (SELECT MAX(ID) FROM 你的目标表名) + ROW_NUMBER() OVER (ORDER BY 业务指定的排序字段) AS ID,
    其他列1,
    其他列2,
    其他列3
FROM 你的查询数据源/关联查询逻辑;

逻辑说明：

• 子查询会先获取当前表的最大ID值，作为ID计算基数
• ROW_NUMBER()会按照OVER子句中指定的排序规则，给查询返回的每一行生成从1开始的连续序号，和基数相加后，第一行ID为MAX(ID)+1、第二行为MAX(ID)+2，正好匹配连续递增的需求
• OVER中的排序字段需根据业务规则选择，比如创建时间、数据源主键等，保证ID生成顺序符合预期

不支持窗口函数的旧版数据库（以MySQL 5.x为例） #

可以通过用户变量累加的方式生成连续ID，写法如下：

-- 先初始化ID基数为当前表最大ID
SET @current_id = (SELECT MAX(ID) FROM 你的目标表名);

INSERT INTO 你的目标表名 (ID, 其他列1, 其他列2, 其他列3)
SELECT
    @current_id := @current_id + 1 AS ID,
    其他列1,
    其他列2,
    其他列3
FROM 你的查询数据源/关联查询逻辑
ORDER BY 业务指定的排序字段;

内容的提问来源于stack exchange，提问作者Mike - SMT