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?
INSERT INTO SELECT statement reads data from one table and inserts it into an existing table. Such as, if we want to copy the Location table data into a temp table using the INSERT INTO SELECT statement, we have to specify the temporary table explicitly and then insert the data.
The OUTPUT clause has access to two temporary or in-memory SQL tables, called INSERTED and DELETED tables. These tables are populated when an INSERT/UPDATE/DELETE operation is done on a table. As a result, the OUTPUT clause can provide us the affected records by referencing these tables. So let's see how to do this.
When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that's been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application.
Use the RETURNING clause. The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM , can be computed. The new (post-update) values of the table's columns are used.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With