SQL Server中为分组表添加不存在指定relatedID的新行

我有一张包含1000多行、30多列的Table1表，核心列说明：

• ID：分组标识，同组ID相同
• relatedID：对应不同人员，每组内唯一
• Name：组名，同组一致

现有数据示例 #

需求 #

为每个ID组添加relatedID为1111的新行，仅当该组不存在此relatedID时执行，其他列保持不变。

预期结果 #

我的尝试代码 #

SELECT     
    identifier, title,
    (FORMAT(registerDate, 'yyyy-MM-dd', 'en-US')) AS Reg_Date,  
    GetText(ID) AS relatedIDName, -- 这个函数传入1111会返回对应的人员名称
    name
INTO 
    #t1
FROM    
    InfoHeap 
INNER JOIN
    ObjProperties op ON objectID = infoHeapID 
INNER JOIN
    InfoHeapCaseType ct ON ct.caseTypeID = InfoHeap.caseTypeID
WHERE    
    moduleID = 100 AND typeID = 199 
    AND registerDate BETWEEN '2020-01-01' AND '2022-12-31'

UPDATE #t1 
SET relatedIDName = CONCAT_WS(',', relatedIDName, 'Dejana') -- Dejana是relatedID=1111对应的人员名
WHERE relatedIDName NOT LIKE '%Dejana%'

-- 这里不知道怎么继续了
-- INSERT into Table1
-- SELECT Distinct t1.*, relatedPropID = 1111 -- Related propID 
-- WHERE relatedPropID NOT LIKE 1111

正确实现方案 #

方法1：用NOT EXISTS判断缺失组 #

直接找出所有未包含relatedID=1111的ID组，插入对应新行：

-- 插入缺少1111的组对应的新行，需补充Table1的所有非自动生成列
INSERT INTO Table1 (ID, relatedID, Name, identifier, title, Reg_Date, relatedIDName)
SELECT DISTINCT 
    t.ID, 
    1111 AS relatedID, 
    t.Name,
    t.identifier,
    t.title,
    t.Reg_Date,
    CONCAT_WS(',', t.relatedIDName, 'Dejana') AS relatedIDName
FROM #t1 t
WHERE NOT EXISTS (
    SELECT 1 
    FROM Table1 
    WHERE ID = t.ID AND relatedID = 1111
)

方法2：先提取唯一组再过滤 #

先获取所有独立的ID组信息，再排除已存在1111的组：

WITH UniqueGroups AS (
    SELECT DISTINCT 
        ID, 
        Name,
        identifier,
        title,
        Reg_Date,
        relatedIDName
    FROM #t1
)
INSERT INTO Table1 (ID, relatedID, Name, identifier, title, Reg_Date, relatedIDName)
SELECT 
    ID, 
    1111, 
    Name,
    identifier,
    title,
    Reg_Date,
    CONCAT_WS(',', relatedIDName, 'Dejana')
FROM UniqueGroups ug
WHERE NOT EXISTS (
    SELECT 1 
    FROM Table1 
    WHERE ID = ug.ID AND relatedID = 1111
)

注意事项 #

1. 插入时必须指定Table1的所有非自动生成列，保证列数和顺序与SELECT语句匹配
2. 若无需临时表，可直接将#t1替换为原查询语句，减少中间步骤
3. 如果relatedIDName可以实时生成，也可直接用CONCAT_WS(',', GetText(ID), 'Dejana')替代临时表中的更新操作

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