How to use output clause in SQL to output insert, update, delete results into new temporary table?

Question

Currently, I'm trying to perform an update in SQL Server (but it could be any DML statement that supports the output clause), and I'd like to put the output into a local temp table, like so:

update
    dbo.MyTable
set
    MyField = 30
output
    inserted.MyKeyField
into
    #myTempTable
from
    dbo.MyTable as t
where
    f.MyFilteredField = 8 

I know the syntax is correct, as per the documentation for the output clause (emphasis mine):

output_table

Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

That said, I'd expect it to work just like it would on the the into clause on a select statement in that it would just create the table.

However, I get the following error:

Invalid object name '#myTempTable'.

How can I get the results of the output clause (inserted or deleted) into a temp table?

Aristophanes

Answer 1

The output clause will not generate a new table, so you have to generate the table beforehand which matches the structure of what is specified in the output clause, for example:

select t.MyKeyField into #myTempTable from dbo.MyTable as t where 1 = 0

Note, you don't have to use the select into syntax above, create table works just as well. In the end, whatever is easiest to create an empty temporary table that matches the fields in your output clause.

Once the table is created, the "Invalid object name" error will go away and the DML statement will execute without error (assuming there are no other errors).

Aristophanes

It's unfortunate that this is the case, since it means picking between losing the convenience of temp table type detection, or significant code duplication. – Lipoprotein