SQL Server自增列插入异常：新行ID未延续现有最大值

现有需求：从已有数据的Table B向Table A插入数据，当前脚本可正确插入其他字段，但ID列不符合预期。Table A现有最大ID为268564，期望新插入行ID从268565开始递增，但当前脚本插入的ID从1开始（如1、2、3…）。

当前使用的SQL脚本 #

set identity_insert Table A ON;
INSERT INTO Table A 
(ID, Field2, Field3, Field4, Field5)

SELECT ROW_NUMBER() OVER (ORDER BY Table A.[ID]) AS Number, Field2, Field3, 0, Field4, Field5

FROM Table B
LEFT JOIN Table A
ON Table A.[match] = Table B.[match]
WHERE 
Table B.[match] = 44593
set identity_insert Table A OFF

示例数据 #

问题原因与解决方案 #

问题原因 #

当前脚本中ROW_NUMBER() OVER (ORDER BY Table A.[ID])仅生成从1开始的连续序号，没有结合Table A的最大ID进行计算，导致插入的ID从1起始。

修改后的脚本 #

-- 获取Table A当前最大ID，处理表为空的边界情况
DECLARE @MaxID INT = (SELECT ISNULL(MAX(ID), 0) FROM Table A);

SET IDENTITY_INSERT Table A ON;
INSERT INTO Table A 
(ID, Field2, Field3, Field4, Field5)
SELECT 
    @MaxID + ROW_NUMBER() OVER (ORDER BY Table B.[match]) AS ID,
    Field2, 
    Field3, 
    0, 
    Field4, 
    Field5
FROM Table B
LEFT JOIN Table A
ON Table A.[match] = Table B.[match]
WHERE 
    Table B.[match] = 44593
    -- 可选：过滤已存在于Table A的记录，避免重复插入
    AND Table A.[ID] IS NULL;
SET IDENTITY_INSERT Table A OFF;

-- 可选：更新Table A的标识种子，避免后续自动生成ID时冲突
DBCC CHECKIDENT ('Table A', RESEED, @MaxID + (SELECT COUNT(*) FROM Table B WHERE [match] = 44593));

关键修改点 #

1. 获取最大ID：通过变量@MaxID获取Table A的当前最大ID，用ISNULL处理表为空的场景，确保起始值正确。
2. 生成递增ID：用@MaxID + ROW_NUMBER()计算新ID，保证从268565开始连续递增。
3. 优化排序逻辑：将排序字段改为Table B.[match]，避免关联Table A后可能导致的序号混乱。
4. 避免重复插入：添加AND Table A.[ID] IS NULL过滤已存在的记录（根据业务需求可选）。
5. 更新标识种子：使用DBCC CHECKIDENT重置标识种子，防止后续自动生成ID时出现重复或断号问题。

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