合并语句中的合并语句在单个 SP 中添加、更新、删除

Question

我正在尝试创建合并语句以在一个SP中执行Insert, Update, Delete，如下所示。

但我的要求是在插入语句时，我需要添加具有不同值的多个插入并面临问题。 删除语句是否有效，还是我需要更改它？

Declare @Project_Id INT =12;
MERGE Table1 AS TARGET
USING Table2 AS SOURCE 
ON (TARGET.Id = SOURCE.Id AND TARGET.Project_Id = SOURCE.Project_Id)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.Name <> SOURCE.Name AND TARGET.Project_Id = @Project_Id 
THEN UPDATE SET TARGET.Name = SOURCE.Name, Target.Project_Id= @Project_Id
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN 
INSERT  (project_id,Financials_Desc,created_date,createdby,Name,Id) Values 
(@PROJECT_ID,'Gross Sales (or BGA) Total - Launch 
Year',CURRENT_TIMESTAMP,@createdby,Source.Name,Source.Id )
INSERT  (project_id,Financials_Desc,created_date,createdby,Name,Id) Values 
(@PROJECT_ID,'Gross Sales (or BGA) Total - 
Ongoing',CURRENT_TIMESTAMP,@createdby,Source.Name,Source.Id )

--When there is a row that exists in target and same record does not exist in source then delete 
this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 

Answer 1

在源字段中，您可以使用查询，如果我是您，我会将目标与表示合并最终结果的查询合并：

MERGE Table1 AS TARGET
USING (select value, value from tableX join tableY ...) AS SOURCE 

另外，请注意，在比较匹配结果时，您需要考虑空值：

when matched and (source.val != target.val or source.val is null and target.val is not null or source.val is not null and target.val is null)