Using OUTPUT INTO with from_table_name in an INSERT statement [duplicate]

Question

Microsoft's OUTPUT Clause documentation says that you are allowed to use from_table_name in the OUTPUT clause's column name.

There are two examples of this:

• Using OUTPUT INTO with from_table_name in an UPDATE statement
• Using OUTPUT INTO with from_table_name in a DELETE statement

Is it possible to also use it in an INSERT statement?

INSERT INTO T ( [Name] )
OUTPUT S.Code, inserted.Id INTO @TMP -- The multi-part identifier "S.Code" could not be bound.
SELECT [Name] FROM S;

Failing example using table variables

-- A table to insert into.
DECLARE @Item TABLE (
    [Id] [int] IDENTITY(1,1),
    [Name] varchar(100)
);
-- A table variable to store inserted Ids and related Codes
DECLARE @T TABLE (
    Code varchar(10),
    ItemId  int
);
-- Insert some new items
WITH S ([Name], Code) AS (
    SELECT 'First', 'foo'
    UNION ALL SELECT 'Second', 'bar'
    -- Etc.
)
INSERT INTO @Item ( [Name] )
OUTPUT S.Code, inserted.Id INTO @T -- The multi-part identifier "S.Code" could not be bound.
SELECT [Name] FROM S;

Jimmie

source columns which are not inserted can be referenced/output only for MERGE – Cornwall

Does this answer your question? Is it possible to for SQL Output clause to return a column not being inserted? – Corinacorine

@Corinacorine Yes, it does! That is a duplicate answer I failed to find. – Jimmie

Answer 1

No, because an INSERT doesn't have a FROM; it has a set of values that are prepared either by the VALUES keyword, or from a query (and even though that query has a FROM, you should conceive that it's already been run and turned into a block of values by the time the insert is done; there is no s.code any more)

If you want to output something from the table that drove the insert you'll need to use a merge statement that never matches any records (so it's only inserting) instead, or perhaps insert all your data into @tmp and then insert from @tmp into the real table - @tmp will thus still be the record of rows that were inserted, it's just that it was created to drive the insert rather than as a consequence of it (caveats that it wouldn't contain calculated columns)

Convict