T-SQL使用MERGE操作table_2时NOT MATCHED分支如何引用table_1的ID插入

错误原因 #

T-SQL 中 MERGE 语句的 NOT MATCHED [BY TARGET] 分支的 INSERT 语法仅支持 VALUES 列表或者 DEFAULT VALUES，不允许直接嵌套 SELECT 查询，这是官方语法限制。

解决方案 #

核心思路是将需要从 table_1 查询的自增 ID 提前合并到 MERGE 语句的源数据集（USING 子句部分）中，NOT MATCHED 分支直接从源数据集取字段拼接 VALUES 即可。
这里提供两种落地写法，按需选择：

写法1：直接在第二个MERGE的USING中关联table_1 #

适合逻辑简单、并发冲突概率低的场景，因为你已经提前执行了table_1的MERGE，保证对应skill_name的ID一定存在：

MERGE
INTO table_2 WITH (HOLDLOCK) AS target
USING (
    SELECT
        '42' AS person_id,
        t1.id AS skill_id
    FROM table_1 t1
    WHERE t1.skill_name = 'skill_1' -- 对应你第一个MERGE中操作的技能名称
) AS source(person_id,skill_id)
ON (target.person_id = source.person_id AND target.skill_id = source.skill_id)
WHEN MATCHED
THEN UPDATE SET  skill_lvl=4,already_have=0
WHEN NOT MATCHED
THEN INSERT (person_id,  skill_id, skill_lvl,already_have)
VALUES (source.person_id, source.skill_id, 3, 1);

写法2：用OUTPUT子句承接table_1生成的ID（更稳妥） #

适合高并发、或者需要严格保证ID准确性的场景，先修改第一个操作table_1的MERGE，用OUTPUT把ID存到变量中，再给第二个MERGE使用：

-- 先操作table_1，拿到对应技能的自增ID
DECLARE @skill_id INT;
MERGE
INTO table_1 WITH (HOLDLOCK) AS target
USING (SELECT 'skill_1' AS skill_name) AS source(skill_name)
ON (target.skill_name = source.skill_name)
WHEN NOT MATCHED
THEN INSERT (category_id,skill_name)  values (0,'skill_1')
-- 输出新插入的ID到变量
OUTPUT inserted.id INTO @skill_id;

-- 如果是已存在的技能，上面的OUTPUT不会返回值，补充查询赋值
IF @skill_id IS NULL
    SELECT @skill_id = id FROM table_1 WHERE skill_name = 'skill_1';

-- 再执行table_2的MERGE，直接用变量里的ID
MERGE
INTO table_2 WITH (HOLDLOCK) AS target
USING (
    SELECT
        '42' AS person_id,
        @skill_id AS skill_id
) AS source(person_id,skill_id)
ON (target.person_id = source.person_id AND target.skill_id = source.skill_id)
WHEN MATCHED
THEN UPDATE SET  skill_lvl=4,already_have=0
WHEN NOT MATCHED
THEN INSERT (person_id,  skill_id, skill_lvl,already_have)
VALUES (source.person_id, source.skill_id, 3, 1);

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