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

sql tsql output-clause
casperOne picture casperOne · May 31, 2012 · Viewed 9.7k times · Source

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?

Answer

casperOne picture casperOne · May 31, 2012

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).

Related questions

@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More