SQL Server中基于MAX值递增字段的表更新与插入问题

表说明 #

• Table 1：最终数据存储表
• Table 2：来自Access的更新数据

Table 1结构与数据

Table 2结构与数据

需求与问题 #

需要实现：用Table 2的数据更新Table 1，当LOCID=LOC_ID、OBJ#=OBJ_#且OBJ_TYPE='A'时，将Table 1的OBJ_V更新为Table 2的OBJV；不匹配的记录则插入到Table 1中。

插入新记录时，要按LOC_ID分组，取该组当前的MAX(OBJ_ID)，为每条新记录生成依次递增的OBJ_ID（除OBJ_TYPE='C'外，OBJ_ID与OBJ_U_ID值相同）。

当前用MERGE语句尝试实现时，插入的所有新记录OBJ_ID都是同一个值，无法生成递增序列，希望插入结果能按LOC_ID分组生成连续递增的OBJ_ID。

当前尝试的MERGE语句 #

MERGE Table1 as T1 Using (
Select @PRoJect as ProN, OBJV, LOCID, OBJ# from TABLE2 where OBJ# is not null) AS S2
on
(T1.OBJ_# = S2.OBJ# and T1.LOC_ID = S2.LOCID and T1.ProJ# = S2.ProN and T1.OBJ_TYPE = 'A')
When Matched then Update Set
    T1.OBJ_V  = S2.OBJV
When Not Matched then Insert (
ProJ# , LOC_ID , OBJ_ID , OBJ_U_ID , OBJ_TYPE , OBJ_#  , OBJ_V
) Values (
@PRoJect , s2.locID , "NEED HELP HERE",  "NEED HELP HERE" , 'A', S2.OBJ#, S2.OBJV
); 

期望插入效果 #

插入后LOC_ID与OBJ_ID对应关系如下：

解决方案 #

问题根源在于MERGE语句无法直接按分组动态生成递增ID，因为插入操作是基于单条源记录计算的，无法实时获取分组后的序列值。可以通过预处理数据源的方式解决，以下是基于SQL Server的实现方案：

-- 1. 预存每个LOC_ID当前的最大OBJ_ID（排除类型C的特殊格式ID）
DROP TABLE IF EXISTS #MaxObjIds;
CREATE TABLE #MaxObjIds (
    LOC_ID INT,
    Max_OBJ_ID INT
);
INSERT INTO #MaxObjIds
SELECT LOC_ID, ISNULL(MAX(OBJ_ID), 0)
FROM Table1
WHERE OBJ_TYPE <> 'C'
GROUP BY LOC_ID;

-- 2. 为待插入的记录分配分组递增的OBJ_ID
DROP TABLE IF EXISTS #StagedData;
CREATE TABLE #StagedData (
    ProJ# VARCHAR(10),
    LOC_ID INT,
    OBJ# INT,
    OBJV INT,
    New_OBJ_ID INT
);
INSERT INTO #StagedData
SELECT 
    @PRoJect AS ProJ#,
    S2.LOCID AS LOC_ID,
    S2.OBJ#,
    S2.OBJV,
    -- 按LOC_ID分组，从当前最大值开始递增分配ID
    mo.Max_OBJ_ID + ROW_NUMBER() OVER (PARTITION BY S2.LOCID ORDER BY S2.OBJ#) AS New_OBJ_ID
FROM TABLE2 S2
LEFT JOIN #MaxObjIds mo ON S2.LOCID = mo.LOC_ID
-- 筛选出Table1中无匹配的待插入记录
LEFT JOIN Table1 T1 
    ON T1.OBJ_# = S2.OBJ# 
    AND T1.LOC_ID = S2.LOCID 
    AND T1.ProJ# = @PRoJect 
    AND T1.OBJ_TYPE = 'A'
WHERE S2.OBJ# IS NOT NULL 
    AND T1.ProJ# IS NULL;

-- 3. 执行MERGE完成更新与插入
MERGE Table1 AS T1
USING (
    SELECT @PRoJect AS ProN, OBJV, LOCID, OBJ# FROM TABLE2 WHERE OBJ# IS NOT NULL
) AS S2
ON (T1.OBJ_# = S2.OBJ# AND T1.LOC_ID = S2.LOCID AND T1.ProJ# = S2.ProN AND T1.OBJ_TYPE = 'A')
WHEN MATCHED THEN 
    UPDATE SET T1.OBJ_V = S2.OBJV
WHEN NOT MATCHED THEN 
    INSERT (ProJ#, LOC_ID, OBJ_ID, OBJ_U_ID, OBJ_TYPE, OBJ_#, OBJ_V)
    VALUES (
        @PRoJect, 
        s2.LOCID, 
        (SELECT New_OBJ_ID FROM #StagedData sd WHERE sd.LOC_ID = s2.LOCID AND sd.OBJ# = s2.OBJ#),
        (SELECT New_OBJ_ID FROM #StagedData sd WHERE sd.LOC_ID = s2.LOCID AND sd.OBJ# = s2.OBJ#),
        'A', 
        S2.OBJ#, 
        S2.OBJV
    );

-- 清理临时表
DROP TABLE IF EXISTS #MaxObjIds;
DROP TABLE IF EXISTS #StagedData;

代码说明 #

• #MaxObjIds临时表存储每个位置分组的当前最大ID，确保新ID从正确的起始值开始递增。
• #StagedData通过ROW_NUMBER()函数按位置分组，为每条待插入记录分配唯一的递增ID。
• MERGE插入阶段直接引用预处理好的ID值，保证同一位置分组内的ID连续递增。

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