Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 666
casperOne Avatar asked May 31 '12 18:05

casperOne


People also ask

How do you insert data 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.

How do I do an output statement in SQL?

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.

What is the use of output in SQL?

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.

How do I return a row that was updated in SQL?

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.


1 Answers

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

like image 124
casperOne Avatar answered Sep 30 '22 15:09

casperOne