在匹配时使用多个语句

Question

我正在使用MERGE语句，以便将XML输入插入到SQL Server数据库表中。如何在WHEN MATCHED块中执行多个条件。请参考下面的代码。

USING TableRelationship AS new
  ON (new.TableRelationshipTypeID = old.TableRelationshipTypeID) AND old.ToRoleID = @RoleID

WHEN MATCHED THEN 
   UPDATE 
       SET old.FromRoleID = new.FromRoleID
   -- Condition 2
   -- Condition 3

当前，WHEN MATCHED它仅执行此old.FromRoleID = new.FromRoleID行。如何执行条件中的所有3行（-- Condition 2 and 3）WHEN NOT MATCHED。

前任 ：

这就是我的期望。WHEN MATCHED我只想更新旧字段（old.ThruDate = GETDATE()）并将记录插入同一张表。我不能用逗号分隔这些语句。SQL发出

语法错误

MERGE INTO Party.TableRelationship AS old
USING TableRelationship AS new ON (new.TableRelationshipTypeID = old.TableRelationshipTypeID) AND old.ToRoleID = @RoleID

WHEN MATCHED THEN 
   UPDATE 
       SET old.ThruDate = GETDATE(),
   INSERT (FromRoleID, ToRoleID, TableRelationshipTypeID)
   VALUES (new.FromRoleID, new.ToRoleID, new.TableRelationshipTypeID);

谢谢你。

Answer 1

您可以使用 INSERT over DML 它来实现：

INSERT INTO tab_name(FromRoleID, ToRoleID, TableRelationshipTypeID)
SELECT FromRoleID, ToRoleID, TableRelationshipTypeID
FROM (
  MERGE INTO Party.TableRelationship AS old
  USING TableRelationship AS new 
     ON new.TableRelationshipTypeID = old.TableRelationshipTypeID 
    AND old.ToRoleID = @RoleID
  WHEN MATCHED THEN 
    UPDATE SET old.ThruDate = GETDATE()
    OUTPUT $action, FromRoleID, ToRoleID, TableRelationshipTypeID
) sub(action, FromRoleID, ToRoleID, TableRelationshipTypeID)
WHERE action = 'UPDATE';

请记住，此方法有一些局限性，更多信息： MS
Connect