我正在尝试学习如何使用 MERGE 运算符。以下代码可以正确编译:
ALTER PROCEDURE moto.procPM_UpdateLines
@LineId As Int = null,
@LineName As Varchar(100),
@DeleteMe As Bit = 0
AS
BEGIN
MERGE moto.tblPMLine AS line
USING (SELECT LineId, LineName FROM moto.tblPMLine) AS existsLine
ON line.LineId = existsLine.LineId
WHEN MATCHED AND @DeleteMe = 1 THEN DELETE
WHEN MATCHED AND @DeleteMe = 0 THEN UPDATE SET line.LineName = @LineName
WHEN NOT MATCHED THEN INSERT(LineName) VALUES(@LineName);
END
GO
我知道这是一个非常简单的过程,但由于某种原因,当我使用以下命令时,它似乎没有生成任何条目。
execute moto.procPM_UpdateLines null, 'First test', 0
有没有办法让我知道它遵循的是哪个选项(如果有的话)?
以前的存储过程已修复。
MERGE INTO moto.tblPMLine AS T
USING (SELECT @LineId as LineId, @LineName as LineName) AS S
ON T.LineId = S.LineId
WHEN MATCHED AND @DeleteMe = 0 THEN --UPDATE
UPDATE SET LineName = @LineName
WHEN MATCHED AND @DeleteMe = 1 THEN --DELETE
DELETE
WHEN NOT MATCHED THEN--INSERT
INSERT (LineName) VALUES (@LineName)
OUTPUT $action AS ChangesMade;
现在它可以插入、更新和删除,并返回输出。
你可以结合MERGE
和OUTPUT
子句来获取某种“活动报告”(或调试“打印语句”)MERGE
正在做的事情 - 也许这会帮助您了解出了什么问题。
请参阅 Adam Machanic 的精彩博客文章输出博士或:我如何学会停止担忧并热爱合并 http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx展示了该技术以及如何使用它
基本上它归结为使用OUTPUT
条款与MERGE
声明来获取有关正在发生的事情的信息 - 大致如下:
MERGE INTO ......
WHEN MATCHED THEN
.......
WHEN NOT MATCHED THEN
......
WHEN NOT MATCHED BY SOURCE THEN
........
OUTPUT
$action AS dml_action,
inserted.x AS new_x,
deleted.x AS old_x,
inserted.y AS new_y,
deleted.y AS old_y;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)