Background

The OUTPUT clause can be used to return results from an INSERT, UPDATE, DELETE, or MERGE statement. The data can be returned to the client, inserted to a table, or both.

There are two ways to add OUTPUT data to a table:

1. Using OUTPUT INTO
2. With an outer INSERT statement.

For example:

-- Test table
DECLARE @Target table
(
    id integer IDENTITY (1, 1) NOT NULL, 
    c1 integer NULL
);
-- Holds rows from the Output clause
DECLARE @Output table 
(
    id integer NOT NULL, 
    c1 integer NULL
);

--
-- Using OUTPUT INTO
--
-- Insert to the target table
INSERT @Target 
    (c1)
    -- Insert to the output table
    OUTPUT
        Inserted.id, 
        Inserted.c1 
    INTO @Output 
        (id, c1)
VALUES (1);

--
-- Using outer INSERT
--
-- Insert to the output table
INSERT @Output 
    (id, c1)
SELECT 
    SQ1.id, SQ1.c1
FROM 
(
    -- Insert to the target table
    INSERT @Target 
        (c1)
    OUTPUT
        -- Returns data to the outer INSERT
        Inserted.id, 
        Inserted.c1
    VALUES (1)
) AS SQ1;

The execution plan is the same for both forms:

Notice there are two Insert operators. The new row is first added to the @Target table by the Clustered Index Insert, then added to the @Output table by the Table Insert operator.