Get merge WHEN NOT MATCHED output into another table

Question

I want to insert the not matched output to two tables. When I try to insert the output of the stored procedure it takes all the updated values as well to the new table not only the not matched values to the 'table'.

ALTER PROCEDURE [dbo].[identify]
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO [test].[dbo].[warehouse] AS dim
    USING [test].[dbo].[staging] AS stg
        ON dim.[first_name] = stg.first_name
    WHEN MATCHED THEN
        UPDATE SET
            dim.[first_name] = stg.first_name,
            dim.last_name = stg.last_name,
            dim.created_date = stg.created_date,
            dim.modified_date = stg.modified_date,
            dim.gender = stg.gender
    WHEN NOT MATCHED THEN
        INSERT(first_name, last_name, created_date, modified_date, gender)
        VALUES(first_name, last_name,created_date, modified_date,gender)
    OUTPUT Inserted.first_name, Inserted.last_name INTO test (first_name,last_name);

END

Answer 1

CREATE OR ALTER PROCEDURE [dbo].[identify]
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @UpdatedRows TABLE
    (
        action NVARCHAR(10),
        first_name NVARCHAR(50),
        old_last_name NVARCHAR(50),
        new_last_name NVARCHAR(50)
    );

    MERGE INTO [dbo].[warehouse] AS dim
    USING [dbo].[staging] AS stg
        ON dim.[first_name] = stg.first_name
    WHEN MATCHED THEN
        UPDATE SET
            [first_name] = stg.first_name,
            last_name = stg.last_name,
            created_date = stg.created_date,
            modified_date = stg.modified_date,
            gender = stg.gender
    WHEN NOT MATCHED THEN
        INSERT(first_name, last_name, created_date, modified_date, gender)
        VALUES(stg.first_name, stg.last_name, stg.created_date, stg.modified_date, stg.gender)
    OUTPUT $action AS action, Inserted.first_name AS first_name, Deleted.last_name AS old_last_name, Inserted.last_name AS new_last_name
    INTO @UpdatedRows;

    INSERT INTO test (action, first_name, last_name)
    SELECT action, first_name, old_last_name
    FROM @UpdatedRows
    WHERE action = 'UPDATE';

    INSERT INTO test (action, first_name, last_name)
    SELECT action, first_name, new_last_name
    FROM @UpdatedRows
    WHERE action = 'INSERT';
END;

Here a table variable @UpdatedRows is declared which records the action performed and can then be used to insert into multiple tables as required.