MERGE语句在SQL Server中的DELETE替代方案

问题描述：

我有一个使用T-SQL MERGE语句的查询。由于性能问题，我正在使用IF Exists Update和If Not Exists Insert来编写查询。我能够写入插入/更新没有任何问题。但我无法处理DELETE。有人可以帮助我吗？MERGE语句在SQL Server中的DELETE替代方案

这里是样品

---SAMPLE MERGE STATEMENT 
MERGE 
    member_topic AS target 
USING 
    someOtherTable AS source 
ON 
    target.mt_member = source.mt_member 

WHEN MATCHED THEN 
    UPDATE SET target.mt_notes = source.mt_notes 
WHEN NOT MATCHED THEN 
    INSERT (mt_member, mt_topic, mt_notes) VALUES (source.mt_member, source.mt_notes) 

WHEN NOT MATCHED BY SOURCE THEN 
DELETE member_topic; 


--UPDATE 

UPDATE T SET T.mt_notes = S.mt_notes 
FROM member_topic T 
JOIN someOtherTable S ON T.mt_member=S.mt_member 


--INSERT 

INSERT INTO member_topic(mt_member, mt_topic, mt_notes) 
SELECT mt_member, mt_topic, mt_notes 
FROM someOtherTable S 
WHERE NOT EXISTS(SELECT 1 
       FROM member_topic T 
       WHERE T.mt_member=S.mt_member)

如何在一个DELETE语句处理

WHEN NOT MATCHED BY SOURCE THEN 
DELETE member_topic;

。

当然请邮寄样本代码你做了什么至今 –

安置自己的数据库结构之间，示例代码，你想要什么，以及你得到了什么。 –

答

一个示例脚本被嵌入开始以及proc

MERGE dbo.Tablet AS TARGET 
    USING dbo.QueryView AS SOURCE 
    ON ( 
     TARGET.[ID] = SOURCE.[ID] 
     ) 

WHEN MATCHED 

    THEN 
UPDATE SET 

TARGET.[ID] = SOURCE.[ID] 

WHEN NOT MATCHED BY TARGET THEN 
INSERT (ID, [Name] ) 
VALUES (SOURCE.[ID], SOURCE.[Name] ) 

WHEN NOT MATCHED BY SOURCE THEN 
DELETE;

如何处理不匹配的源码然后 DELETE;在单DELETE语句 – Ram

@Ram如果你正在做增量加载然后删除命令不是必需的。单条语句足以删除所有与源不匹配的记录，除非您想要删除特定的行 –

AFAIU OP努力用3个单独的语句INSERT，UPDATE，DELETE替代单个MERGE语句。 – Serg

答

结束尝试

DELETE T 
FROM member_topic T 
WHERE NOT EXISTS(SELECT 1 
       FROM someOtherTable S 
       WHERE T.mt_member=S.mt_member)

答

DELETE t 
FROM member_topic t 
    LEFT JOIN someOtherTable s ON t.mt_member = s.mt_member 
WHERE s.mt_member IS NULL

MERGE语句在SQL Server中的DELETE替代方案

相关推荐