时间戳

首页 > 解决方案 > T-SQL：合并插入旧值和新值，然后进行更改

sql-server - T-SQL：合并插入旧值和新值，然后进行更改

问题描述

这是我第一次使用 T-SQL Merge，我一直在尝试使用以下条件编写存储过程：

  1. Changes如果存在记录，则将Id、旧值（更新前）和新值插入表中，然后更新旧值Affected

  2. 如果记录不存在，则插入ChangesId，旧值和新值为空，然后插入新记录Affected

  3. 如果记录不匹配，则插入ChangesID、旧值（删除前的值）并作为新值清空，然后删除记录

这是我使用的表格

CREATE TABLE Affected
(
    [Id] int IDENTITY(1,1) NOT NULL,
    [ZoneId] int NOT NULL,
    [Name] varchar(100) NOT NULL,
    [Value] varchar(100)
)

CREATE TABLE Changes
(
    [AffectedId] int NOT NULL,
    [OldValue] varchar(100),
    [NewValue] varchar(100)
)

这是我的存储过程，它以两个区域 ID 作为输入参数

CREATE PROCEDURE spAffectChanges
    @ZoneId1 int,
    @ZoneId2 int
AS
    IF(@ZoneId1 < 10)
    BEGIN
        ;WITH fromQ AS 
        (
            SELECT DISTINCT Name, Value 
            FROM Affected 
            WHERE ZoneId = @ZoneId1 AND Name NOT IN ('aaa', 'bbb', 'ccc')
        ), toQ AS
        (
            SELECT DISTINCT Name, Value 
            FROM Affected 
            WHERE ZoneId = @ZoneId2 AND Name NOT IN ('aaa', 'bbb', 'ccc')
        )
        MERGE toQ
        USING fromQ ON (toQ.Name = fromQ.Name)

        WHEN MATCHED AND fromQ.Value<>toQ.Value THEN
            ----first insert
            INSERT INTO Changes (AffectedId, OldValue, NewValue)
            VALUES(toQ.Id, toQ.Value, fromQ.Value)
            ----then update
            UPDATE SET toQ.Value=fromQ.Value

        WHEN NOT MATCHED BY toQ THEN
            ----first insert
            INSERT INTO Changes (AffectedId, OldValue, NewValue)
            VALUES(toQ.Id, '', fromQ.Value)
            ----second insert 
            INSERT (Name, Zone, Value)
            VALUES (fromQ.Name, @ZoneId1, fromQ.Value)

        WHEN NOT MATCHED BY fromQ THEN
            ----first insert
            INSERT INTO Changes (AffectedId, OldValue, NewValue)
            VALUES(toQ.Id, toQ.Value, '')
            ----then delete
            DELETE
    END

有没有办法同时执行插入更改和更新/插入/删除Affected？

提前致谢

标签： sql-serverstored-proceduressql-merge

解决方案

您可以使用该OUTPUT子句来执行此操作。

笔记：

  • MERGE 需要分号终止符。;WITH很傻，;不是起始符，它应该放在每个命令的末尾
  • 的选项WHEN NOT MATCHED是BY SOURCE和BY TARGET（这是默认设置）
  • 我不太明白你是如何DISTINCT实现目标的，我不相信这是允许的，也不认为这样做有什么意义。
        WITH fromQ AS 
        (
            SELECT DISTINCT Name, Value 
            FROM Affected 
            WHERE ZoneId = @ZoneId1 AND Name NOT IN ('aaa', 'bbb', 'ccc')
        ), toQ AS
        (
            SELECT Name, Value 
            FROM Affected 
            WHERE ZoneId = @ZoneId2 AND Name NOT IN ('aaa', 'bbb', 'ccc')
        )
        MERGE toQ
        USING fromQ ON (toQ.Name = fromQ.Name)

        WHEN MATCHED AND fromQ.Value <> toQ.Value THEN
            UPDATE
                SET toQ.Value = fromQ.Value

        WHEN NOT MATCHED BY TARGET THEN
            INSERT (Name, Zone, Value)
            VALUES (fromQ.Name, @ZoneId1, fromQ.Value)

        WHEN NOT MATCHED BY SOURCE THEN
            DELETE

        OUTPUT inserted.Id, ISNULL(deleted.Value, ''), ISNULL(inserted.Value, '')
            INTO Changes (AffectedId, OldValue, NewValue)
        ;

推荐阅读