SQL Server如何在记录不存在时向Contacting表插入关联People表的联系方式数据

方案1：分两次独立INSERT（推荐，逻辑简单兼容性强） #

邮箱和网站的插入规则完全独立，分开处理可以规避多条件判断的混乱，也适配Contacting表的联合主键约束，所有支持标准SQL的数据库均可使用：

插入邮箱记录 #

INSERT INTO Contacting (ID, WAY_OF_CONTACTING, REFERENCE)
SELECT 
    p.ID,
    'Email' AS WAY_OF_CONTACTING,
    p.EMAIL AS REFERENCE
FROM People p
WHERE 
    p.EMAIL IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 FROM Contacting c 
        WHERE c.ID = p.ID AND c.WAY_OF_CONTACTING = 'Email'
    );

插入网站记录 #

INSERT INTO Contacting (ID, WAY_OF_CONTACTING, REFERENCE)
SELECT 
    p.ID,
    'Website' AS WAY_OF_CONTACTING,
    p.WEBSITE AS REFERENCE
FROM People p
WHERE 
    p.WEBSITE IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 FROM Contacting c 
        WHERE c.ID = p.ID AND c.WAY_OF_CONTACTING = 'Website'
    );

该写法完全匹配你的业务规则，每次只处理一种联系方式类型，不会互相干扰，无论People表的字段是哪种空值组合都能正确执行，不会触发联合主键冲突。

方案2：单条MERGE语句实现（适用于Oracle/SQL Server等支持MERGE的数据库） #

如果需要单次语句完成全量操作，需要先将People表的每条记录拆分为对应邮箱、网站的两条数据（过滤空值）再做匹配：

MERGE INTO Contacting c
USING (
    SELECT ID, 'Email' AS WAY, EMAIL AS REF FROM People WHERE EMAIL IS NOT NULL
    UNION ALL
    SELECT ID, 'Website' AS WAY, WEBSITE AS REF FROM People WHERE WEBSITE IS NOT NULL
) p
ON (c.ID = p.ID AND c.WAY_OF_CONTACTING = p.WAY)
WHEN NOT MATCHED THEN
    INSERT (ID, WAY_OF_CONTACTING, REFERENCE)
    VALUES (p.ID, p.WAY, p.REF);

常见错误排查 #

之前使用MERGE/IF NOT EXISTS未得到正确结果，大概率是以下两个原因：

• 没有将单条People记录拆分对应两种联系方式类型，导致一次匹配只能处理一种类型，漏掉另一种
• 关联判断是否存在的条件仅匹配了ID，未带上WAY_OF_CONTACTING字段，导致存在性判断逻辑错误

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