表中存在PaymentRecommendationId列却提示无效列名错误的技术咨询

我一眼就看出问题出在哪了——你在MERGE语句的USING数据源里没把PaymentRecommendationId列包含进来！虽然你在OPENJSON的WITH子句里定义了这个字段，但后续的SELECT列表里漏掉了它，导致当你在匹配条件AND PR.PaymentRecommendationId = InputJSON.PaymentRecommendationId里引用它时，系统根本找不到这个列。

问题拆解 #

你看这段代码：

SELECT ClaimId, CoverageTypeId, Payee, IndemnityPmtRequestAmnt, ExpensePaymentRequestAmnt, RequestDate, Memo, CheckNumber, IssueDate, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy 
FROM OPENJSON(@PaymentRecommendationsJson) 
WITH ( 
    PaymentRecommendationId BIGINT, 
    ClaimId BIGINT, 
    CoverageTypeId SMALLINT, 
    Payee VARCHAR(256), 
    IndemnityPmtRequestAmnt MONEY, 
    ExpensePaymentRequestAmnt MONEY, 
    RequestDate DateTime, 
    Memo VARCHAR(512), 
    CheckNumber VARCHAR(16), 
    IssueDate DATETIME,
    CreatedOn DATETIME,
    CreatedBy VARCHAR(64),
    ModifiedOn DATETIME,
    ModifiedBy VARCHAR(64)
)

OPENJSON确实解析了PaymentRecommendationId，但SELECT里没把它捞出来，那InputJSON这个数据源里自然就没有这个列，报错是必然的。

修复方案 #

1. 先把PaymentRecommendationId加到SELECT列表里 #

这是最关键的一步，让USING的数据源包含这个列：

SELECT PaymentRecommendationId, ClaimId, CoverageTypeId, Payee, IndemnityPmtRequestAmnt, ExpensePaymentRequestAmnt, RequestDate, Memo, CheckNumber, IssueDate, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy 
FROM OPENJSON(@PaymentRecommendationsJson) 
WITH ( 
    PaymentRecommendationId BIGINT, 
    ClaimId BIGINT, 
    CoverageTypeId SMALLINT, 
    Payee VARCHAR(256), 
    IndemnityPmtRequestAmnt MONEY, 
    ExpensePaymentRequestAmnt MONEY, 
    RequestDate DateTime, 
    Memo VARCHAR(512), 
    CheckNumber VARCHAR(16), 
    IssueDate DATETIME,
    CreatedOn DATETIME,
    CreatedBy VARCHAR(64),
    ModifiedOn DATETIME,
    ModifiedBy VARCHAR(64)
)

2. 确认MERGE的匹配逻辑符合业务需求 #

• 如果是更新操作：用PaymentRecommendationId匹配目标表和源数据是合理的，比如完整的MERGE结构可以是：

  MERGE INTO PaymentRecommendations AS PR
  USING (...) AS InputJSON
  ON PR.PaymentRecommendationId = InputJSON.PaymentRecommendationId
  WHEN MATCHED THEN
      UPDATE SET 
          ClaimId = InputJSON.ClaimId,
          CoverageTypeId = InputJSON.CoverageTypeId,
          -- 其他需要更新的字段
  WHEN NOT MATCHED THEN
      INSERT (ClaimId, CoverageTypeId, Payee, ...) -- 注意：如果PaymentRecommendationId是自增主键，这里不要写这个列
      VALUES (InputJSON.ClaimId, InputJSON.CoverageTypeId, InputJSON.Payee, ...);
  

• 如果PaymentRecommendationId是自增列，插入时不需要从JSON里取值，那你可以把OPENJSON里的定义改成PaymentRecommendationId BIGINT NULL，避免JSON里没有这个字段时报错。

3. 额外验证步骤 #

• 先确认表中确实有这个列，执行这条语句检查：

  SELECT COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'PaymentRecommendations' 
    AND COLUMN_NAME = 'PaymentRecommendationId';
  

• 检查传入的JSON数据里，更新场景下必须包含PaymentRecommendationId字段，插入场景如果是自增则可以省略。

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