为何空表下MERGE语句的UPSERT操作无法执行插入？

问题根源 #

你的MERGE语句无法执行插入的核心原因是USING子句的数据源为空：当目标表fact没有任何数据时，select * from fact where ...返回的结果集是空的，MERGE操作没有源数据可匹配，因此既不会触发更新逻辑，也不会触发插入逻辑。

修正后的MERGE代码 #

将USING子句改为直接构造要匹配的单行数据（而非从空表查询），确保无论目标表是否为空，源数据集都存在：

merge into fact as f
using (
    select 
        429 as date_id, 
        432 as region_id, 
        5 as attack_id, 
        11 as target_id, 
        12 as gname_id, 
        12 as weapon_id, 
        1 as success, 
        1 as claimed, 
        0 as ishostkid
) as t
on (
    f.date_id = t.date_id 
    and f.region_id = t.region_id 
    and f.attack_id = t.attack_id 
    and f.target_id = t.target_id 
    and f.gname_id = t.gname_id 
    and f.weapon_id = t.weapon_id 
    and f.success = t.success 
    and f.claimed = t.claimed 
    and f.ishostkid = t.ishostkid
)
when MATCHED then
    update set num_attack = f.num_attack + 1
when not matched by target then
    insert (date_id, region_id, attack_id, target_id, gname_id, weapon_id, success, claimed, ishostkid, num_attack)
    values (t.date_id, t.region_id, t.attack_id, t.target_id, t.gname_id, t.weapon_id, t.success, t.claimed, t.ishostkid, 1);

关键优化点 #

• USING子句直接生成目标匹配行，保证源数据始终存在
• INSERT部分引用USING子句的字段值，避免硬编码重复数据
• 明确指定INSERT的列名，降低表结构变更带来的风险

Python+ODBC动态参数适配 #

如果使用Python的ODBC模块（如pyodbc），可以将参数改为动态占位符?，代码示例如下：

import pyodbc

# 建立数据库连接（需根据实际环境修改配置）
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=user;PWD=password')
cursor = conn.cursor()

# 带动态参数的MERGE语句
merge_sql = """
merge into fact as f
using (
    select 
        ? as date_id, 
        ? as region_id, 
        ? as attack_id, 
        ? as target_id, 
        ? as gname_id, 
        ? as weapon_id, 
        ? as success, 
        ? as claimed, 
        ? as ishostkid
) as t
on (
    f.date_id = t.date_id 
    and f.region_id = t.region_id 
    and f.attack_id = t.attack_id 
    and f.target_id = t.target_id 
    and f.gname_id = t.gname_id 
    and f.weapon_id = t.weapon_id 
    and f.success = t.success 
    and f.claimed = t.claimed 
    and f.ishostkid = t.ishostkid
)
when MATCHED then
    update set num_attack = f.num_attack + 1
when not matched by target then
    insert (date_id, region_id, attack_id, target_id, gname_id, weapon_id, success, claimed, ishostkid, num_attack)
    values (t.date_id, t.region_id, t.attack_id, t.target_id, t.gname_id, t.weapon_id, t.success, t.claimed, t.ishostkid, 1);
"""

# 动态参数列表
params = (429, 432, 5, 11, 12, 12, 1, 1, 0)

# 执行并提交
cursor.execute(merge_sql, params)
conn.commit()

# 关闭连接
cursor.close()
conn.close()

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