SELECT INTO结合IDENTITY函数动态获取最大值作为种子的实现方案

现有以(i_number + c_id)为联合键的业务表，需要基于原表数据生成新表，规则如下：

• 指定原数据所属c_id为'001'，新生成数据所属c_id为'901'
• 筛选原表中c_id='001'的所有行写入新表
• 新表中c_id='901'对应的i_number起始值为原表c_id='001'分组下的max(i_number)+1，后续逐行递增（示例中原表c_id='001'的最大i_number为3，新表i_number从4开始连续递增）
• 新表c_id字段固定赋值为'901'，赋值逻辑无异常

示例参考 #

• 原表数据：
• 预期生成结果：

最初尝试使用SELECT...INTO搭配IDENTITY函数实现需求，编写代码如下：

SELECT 
    IDENTITY(INT, 
        (SELECT MAX(i_number) + 1 FROM table_fake WHERE c_id = '001'),
        1) AS i_number,
    '901' AS c_ooo, 
    m_value
INTO [table_fake_new]
FROM table_fake 
WHERE c_id = '001'

实际运行时发现SQL Server的IDENTITY函数不支持传入子查询结果、变量作为种子参数，且不希望通过sp_executesql执行动态SQL的方式实现，需要无动态SQL的可行方案。

不需要依赖IDENTITY函数，直接用窗口函数ROW_NUMBER()生成连续递增序列，叠加计算得到的起始值即可，完全规避动态SQL和IDENTITY的参数限制。

写法1（变量预计算起始值） #

-- 预计算新序列的起始i_number
DECLARE @start_i INT = (SELECT MAX(i_number) + 1 FROM table_fake WHERE c_id = '001');

SELECT
    @start_i + ROW_NUMBER() OVER (ORDER BY i_number) - 1 AS i_number,
    '901' AS c_id,
    m_value
INTO table_fake_new
FROM table_fake
WHERE c_id = '001';

注：OVER子句中的ORDER BY i_number用于保证新生成的i_number递增顺序和原表c_id='001'下的i_number排序一致，如果不需要保留原顺序，可替换为ORDER BY (SELECT 1)按表默认读取顺序生成序列。

写法2（无需声明变量） #

如果不想单独声明变量，可直接将起始值计算逻辑写入SELECT子句，写法更简洁：

SELECT
    (SELECT MAX(i_number) + 1 FROM table_fake WHERE c_id = '001') 
        + ROW_NUMBER() OVER (ORDER BY i_number) - 1 AS i_number,
    '901' AS c_id,
    m_value
INTO table_fake_new
FROM table_fake
WHERE c_id = '001';

以上两种写法均兼容SQL Server 2008及以上版本，生成的i_number连续无跳号，满足联合键的唯一性要求。

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