How to write output out of a delete and update into a table variable?

Problem: How to write output out of a delete and update into a table variable?

Solution: OUTPUT Clause

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Example:

In this article, I am going to show we can get output of updated row from a table.

For example, we updated the quantity of an order. I wanted to show the output of the row that gets updated.

Declare @invoice table

(invoiceid int,

itemid int,

invoicedate datetime

)

Update dbo.Invoice

SET quantity=10

OUTPUT INSERTED.Invoiceid,INSERTED.itemid,INSERTED.invoicedate into @invoice

WHERE Invoiceid=578267

Select * from @invoice

Results:

invoiceid	itemid	invoicedate
578267	786	1930-06-24 00:00:00.000

Example 2:

Similar to the update you can also get what gets deleted into a table variable to output to a resultset.

Declare @invoice table

(invoiceid int,

itemid int,

invoicedate datetime

)

DELETE dbo.Invoice

OUTPUT deleted.Invoiceid,deleted.itemid,deleted.invoicedate into @invoice

WHERE Invoiceid=610723

Select * from @invoice

 Results:

invoiceid	itemid	invoicedate
610723	917	1916-02-14 00:00:00.000