如何在SQL Server中调试合并操作？

Question

我正在尝试学习如何使用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;

现在它可以插入、更新和删除数据，并返回输出。

- Jesus Zamora

---

我的回答是否表明您没有使用@LineID变量？ :) - shahkalpesh

Answer 1

你可以将MERGE和OUTPUT子句结合起来，以获得一种"活动报告"（或调试"打印语句"），以查看MERGE正在执行的操作 - 或许这有助于你了解出了什么问题。

详见Adam Machanic的博客文章《Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE》，介绍了该技术以及如何使用它。

基本上，它归结为在MERGE语句中使用OUTPUT子句来获取操作信息 - 大致类似于：

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;

- marc_s

---

非常感谢，现在我知道怎么做了。 - Jesus Zamora

这个链接要求我登录该网站，但没有注册选项。真是太扯了！这里有一个备用的网址：http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge/ - Tony_Henrich

Answer 2

MERGE moto.tblPMLine AS line
USING (SELECT LineId, LineName FROM moto.tblPMLine WHERE LineID = @LineID) AS existsLine

请原谅我没有使用过 MERGE。然而，我没有看到代码使用 @LineID 变量。

- shahkalpesh