SQL Server插入数据时为Location分组生成LocationCounter计数的方法咨询

在SQL Server中，可通过**窗口函数ROW_NUMBER()**实现需求：按Location分组，为每组内的记录生成从1开始的递增序号，填充LocationCounter字段。

修改后的插入脚本 #

INSERT INTO TargetTable (ID, Location, LocationCounter, Drawer1, Drawer2)
SELECT 
    ID,
    Element1 AS Location,
    -- 按Location分组，以源表ID排序生成递增序号
    ROW_NUMBER() OVER (PARTITION BY Element1 ORDER BY ID) AS LocationCounter,
    Element2 AS Drawer1,
    Element3 AS Drawer2
FROM SourceTable;

关键逻辑说明 #

• PARTITION BY Element1：将源表数据按Element1（对应目标表的Location）分组，确保序号仅在同一Location内递增。
• ORDER BY ID：指定每组内记录的排序依据，这里使用源表的ID保证顺序与源表一致；若有业务层面的排序需求，可替换为对应字段（如时间戳等）。

导入后目标表结果 #

执行脚本后，目标表的LocationCounter字段会被正确填充：

ID    Location   LocationCounter   Drawer1   Drawer2
----------------------------------------------------
 1      43582          1              1         7
 2      46852          1              2         6
 3      46852          2              4         4
 4      47895          1              2         9

增量导入补充（可选） #

如果后续需要增量导入数据，需规避已存在的Location序号重复问题，可修改序号生成逻辑：

INSERT INTO TargetTable (ID, Location, LocationCounter, Drawer1, Drawer2)
SELECT 
    ID,
    Element1 AS Location,
    ROW_NUMBER() OVER (PARTITION BY Element1 ORDER BY ID) 
    + ISNULL((SELECT MAX(LocationCounter) FROM TargetTable WHERE Location = Element1), 0) AS LocationCounter,
    Element2 AS Drawer1,
    Element3 AS Drawer2
FROM SourceTable
-- 过滤已导入的记录
WHERE ID NOT IN (SELECT ID FROM TargetTable);

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