How to use Output Clause?

It returns information from each row affected by any DML operation [INSERT/UPDATE/DELETE], it means you can use it with only DML statements. These results can be retuned to the processing application as confirmation message or archiving purpose, and they can be inserted into a physical or temporary or memory table.

You can get all columns [*] or you can specify the columns to be returned by this clause.

We have two column prefixes DELETED and INSERTED. DELETED returns deleted rows by UPDATE and DELETE operations, while INSERTED returns newly inserted values/rows by INSERT or UPDATE statement.

Syntax:
OUTPUT [column list to be returned] INTO [table/table variable/temporary table] [(Column list)]

Note: You can use OUTPUT clause without INTO clause, just to return data to the processing application, if you want to save/insert affected data into any historical or archival table then you have to use INTO clause.

Example:
-- Create Temporary Table
Create Table #Table1
(
ID int
,val1 int
,val2 int
)

-- Create history table
Create Table #Actions_Performed_ON_Table1
(
ID int
,val1 int
,val2 int
,Actions varchar(20)
,Date DateTime
)

-- INSERT Dummy Data
BEGIN
Declare @ID int, @val1 int, @val2 int
SELECT @ID=isnull(@ID,0)+1, @Val1=isnull(@Val1,0)+1, @Val2=isnull(@val2,0)+1
INSERT into #Table1
Select @ID+ID, @val1+val1, @val2+val2 From #Table1
Go 5
END

-- Using output clause with INSERT Statement to capture new inserted values
INSERT into #table1(ID,val1,val2)
Output INSERTED.ID, INSERTED.val1, INSERTED.val2, 'INSERT', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
values(10,10,10)

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Using output clause with UPDATE Statement to capture deleted data
Update t Set Id = ID + 1, val1 = Val1 + 1, val2 = val2 + 1
Output DELETED.ID, DELETED.val1, DELETED.val2, 'UPDATE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
From #table1 t
Where Id=10

Select * From #table1 Where Id = 11
Select * From #Actions_Performed_ON_Table1

-- Using output clause with UPDATE Statement to capture new inserted data
Update t Set Id = ID - 1, val1 = Val1 - 1, val2 = val2 - 1
Output INSERTED.ID, INSERTED.val1, INSERTED.val2, 'UPDATE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
From #table1 t
Where Id=11

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Using output clause with Delete Statement to capture deleted data
Delete From #table1
Output DELETED.ID, DELETED.val1, DELETED.val2, 'DELETE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
Where Id=10

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Drop temporary tables
Drop Table #table1
Drop Table #Actions_Performed_ON_Table1