OUTPUT clause in SQL Server -

OUTPUT clause in SQL Server enables us to return the result set of data modification operations such as (INSERT, UPDATE, and DELETE). Depending on the data modification operation the column name need a prefix of (inserted. or deleted.)
Let us see what an output clause is and where it could be advantageous for us in action:
OUTPUT with INSERT
With insert statement we could have the output operation to return the inserted value using inserted.[column name]. This way we could see what values inserted to the table.

insert into dbo.tContact (FirstName, LastName,City)
	output inserted.Firstname, inserted.LastName,inserted.City
	values ('Biz','Nigatu','Nazareth' ),
		   ('Jason','Nigatu','Denver');

We could also use the OUTPUT clause together with INTO clause to log the output to a table and we could have more than one OUTPUT clause at the same time.

--output with insert and into clauses
insert into dbo.tContact (FirstName, LastName,City)
--Output to log table
	output inserted.Firstname, inserted.LastName,inserted.City 
	   into dbo.tLogs (FirstName, LastName,City)
	--Output for users to see
	output inserted.Firstname, inserted.LastName,inserted.City	
values ('Biz','Nigatu','Nazareth' ),
	('Jason','Nigatu','Denver');

OUTPUT with Delete
Output clause can be used delete statement as deleted.[columnName]

--output with delete
delete from dbo.tContact
	output deleted.FirstName
	where FirstName = 'Biz';

In the same way as above INTO clause can be used to log the deleted records in a different table

--output with delete with into 
delete from dbo.tContact
	output deleted.Firstname, deleted.LastName,deleted.City 
	   into dbo.tLogs (FirstName, LastName,City)
	output deleted.Firstname, deleted.LastName,deleted.City 
	where FirstName = 'Jason';

OUTPUT with Update
Update statements is described by a combination of insert and delete operations. Where insert operation represent the new value and delete represent the existing value that will be replaced by the new one.

--output with update
update dbo.tContact set FirstName = 'Jase'
	output deleted.FirstName as OriginalValue, 
	       inserted.FirstName as UpdatedValue
	where FirstName = 'Jason';

Sometimes we may have to know what action affected the row especially working in data waarehouse ETL and we could use $action function to get which action affected the row.

MERGE Customers
USING tSource 
ON tSource.ID = Customers.ID
WHEN MATCHED THEN
	UPDATE SET FirstName = tSource.FirstName,
			   LastName  = tSource.LastName
WHEN NOT MATCHED THEN 
	INSERT (ID,FirstName, LastName) VALUES (tSource.ID, tSource.FirstName, tSource.LastName)
OUTPUT DELETED.ID, DELETED.FirstName, Deleted.LastName, $action 
	INTO tLog (ID,FirstName,LastName,Operation);
 
--Check what's inside the log table
SELECT * 
FROM tLog;