如何在 SQL Server 2008 中使用 MERGE 命令从源中删除? [英] How to delete from source using MERGE command in SQL Server 2008?

问题描述

我目前正在使用以下合并代码将日期从源迁移到目标.我有一个新要求，即扩展以下代码以在目标上执行更新/插入后从源中删除记录.这是否可以使用合并(我在网上看到的所有示例都在目标上执行了删除/插入/更新，而不是在源上)

I am currently using the below merge code to migrate date from source to target. I have a new requirement to extend the below code to delete the record from source once an update/insert is performed on the target. Is this possible using merge(all the examples i see on the net had performing del/insert/update in the target not on the source)

    MERGE Target1 AS T
USING Source1 AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE  ;

推荐答案

您可以使用 output 子句将修改/插入的行捕获到表变量中，并在合并后与删除语句一起使用.

You can use the output clause to capture the modified/inserted rows to a table variable and use that with a delete statement after the merge.

DECLARE @T TABLE(EmployeeID INT);

MERGE Target1 AS T
USING Source1 AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE  
OUTPUT S.EmployeeID INTO @T;

DELETE Source1
WHERE EmployeeID in (SELECT EmployeeID
                     FROM @T);

这篇关于如何在 SQL Server 2008 中使用 MERGE 命令从源中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！