Q: T-SQL : merge insert old and new value then make changes It's my first time working with T-SQL Merge and I have been trying to write a Stored Procedure using with the following condition:

*

*If record is present then insert into Changes table the Id, old value (before update) and the new value and after that update the old value from Affected


*if record is not present then insert into Changes the Id, empty for old and the new value and after that insert new record in Affected


*if record didn't match then insert into Changes the Id, old value (value before deletion) and empty as new value and after that delete the record
Here are the tables I used
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)
)

And here is my stored procedure which takes two zone ids as input parameters
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

Is there a way to perform both insert to Changes and Update/Insert/Delete for Affected?
Thanks in advance

A: You can use the OUTPUT clause to do this.
Notes:

*

*MERGE requires a semi-colon terminator. ;WITH is silly, ; is not a beginning-ator, it is meant to be placed at the end of every command

*The options for WHEN NOT MATCHED are BY SOURCE and BY TARGET (this is the default)

*I don't quite understand how you have a DISTINCT on the target, I don't believe it is allowed, nor how it makes sense to do so.

        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)
        ;

