SQL Server 2008 - MERGE 语句 - 在 WHEN MATCHED 块中执行多个操作

发布于 2024-11-07 22:39:20 字数 720 浏览 0 评论 0原文

我正在尝试使用 MERGE 语句来完成以下任务。我有一个 SP，我正在向它传递 TableValue 参数。这就是我的 SP 的样子：

CREATE PROC sp_AddInformation
@IntoTbl dbo.Information  READONLY ,
@baseEventType dbo.EventType READONLY 

AS

BEGIN

MERGE Information
USING (SELECT InfoID, NewsID, NewsType FROM @IntoTbl ) AS baseInfo (InfoID, NewsID, NewsType)
ON (info.infoID = baseInfo.InfoID)
WHEN MATCHED
        THEN 
        --EXEC dbo.sp_insertEventInfo(@baseEventType) (This is not working)

        UPDATE set Info.Reporter = baseInfo.Reporter
WHEN NOT MATCHED BY SOURCE
        THEN 

        DELETE
WHEN NOT MATCHED BY TARGET
    THEN INSERT VALUES (InfoID, NewsID,NewsType);
END

有谁知道我如何调用另一个 SP 或在 WHEN MATCHED 块中的其他表上执行另一个 MERGE？

原文

I am trying to use MERGE statement to accomplish the following. I have a SP and i am passing TableValue Parameter to it. This is how my SP looks like:

CREATE PROC sp_AddInformation
@IntoTbl dbo.Information  READONLY ,
@baseEventType dbo.EventType READONLY 

AS

BEGIN

MERGE Information
USING (SELECT InfoID, NewsID, NewsType FROM @IntoTbl ) AS baseInfo (InfoID, NewsID, NewsType)
ON (info.infoID = baseInfo.InfoID)
WHEN MATCHED
        THEN 
        --EXEC dbo.sp_insertEventInfo(@baseEventType) (This is not working)

        UPDATE set Info.Reporter = baseInfo.Reporter
WHEN NOT MATCHED BY SOURCE
        THEN 

        DELETE
WHEN NOT MATCHED BY TARGET
    THEN INSERT VALUES (InfoID, NewsID,NewsType);
END

Does anyone know how can i call another SP or perform another MERGE on other tables in WHEN MATCHED block?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

来日方长 2024-11-14 22:39:20

无法从 when Matched 块调用存储过程或合并。您只能进行更新或删除（或两者）。来自合并的文档。

 <merge_matched>::=
     { UPDATE SET <set_clause> | DELETE }

您可以使用 output 子句捕获 < 中更新的行代码>匹配时。可以在表变量中捕获输出，然后可以在另一个合并语句或存储过程中使用该输出。在输出中使用 inserted.* 和 $action。 匹配时中的行位于$action = 'UPDATE'

It is not possible to call a stored procedure or merge from the when matched block. You are only allowed to do update or delete (or both). From the documentation on merge.

 <merge_matched>::=
     { UPDATE SET <set_clause> | DELETE }

You can use the output clause to capture the rows that was updated in when matched. The output can be captured in a table variable that you then can use in another merge statement or in a stored procedure. Use inserted.* and $action in the output. The rows from when matched is where $action = 'UPDATE'

回复收藏 0 原文