OUTPUT Clause

SQL QUERY :

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblOutPut ( OutputAction VARCHAR(20), ID INT, GroupID INT, NewValueName VARCHAR(10), OldValueName VARCHAR(10) )
    
    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    
    --- Inserted
    BEGIN
        INSERT INTO #tblTarget
                OUTPUT 
                    'Inserted' AS OutputAction
                    , INSERTED.ID 
                    , INSERTED.GroupID
                    , INSERTED.Name
                    , NULL
                INTO #tblOutPut
            SELECT * FROM #tblSource
            UNION ALL
            SELECT ID + 10, GroupID, Name FROM #tblSource;
    END
    
    --- Updated
    BEGIN
        UPDATE #tblTarget
        SET Name = CAST(ID AS VARCHAR) + ' : ' + Name
        OUTPUT 
            'Updated' AS OutputAction
            , INSERTED.ID 
            , INSERTED.GroupID
            , INSERTED.Name
            , deleted.Name
        INTO #tblOutPut;
    END
    
    --- Deleted
    BEGIN
        DELETE FROM #tblTarget
            OUTPUT 
                'Deleted' AS OutputAction
                , deleted.ID 
                , deleted.GroupID
                , null
                , deleted.Name
            INTO #tblOutPut
        WHERE ID >= 10;
    END
    
    SELECT * FROM #tblOutPut

    DROP TABLE #tblSource
    DROP TABLE #tblTarget
    DROP TABLE #tblOutPut

Output :

OutputAction         ID          GroupID     NewValueName OldValueName
-------------------- ----------- ----------- ------------ ------------
Inserted             1           1           a11          NULL
Inserted             2           1           a21          NULL
Inserted             3           1           a31          NULL
Inserted             11          1           a11          NULL
Inserted             12          1           a21          NULL
Inserted             13          1           a31          NULL
Updated              1           1           1 : a11      a11
Updated              2           1           2 : a21      a21
Updated              3           1           3 : a31      a31
Updated              11          1           11 : a11     a11
Updated              12          1           12 : a21     a21
Updated              13          1           13 : a31     a31
Deleted              11          1           NULL         11 : a11
Deleted              12          1           NULL         12 : a21
Deleted              13          1           NULL         13 : a31