In many database applications, it is common to generate a set of rows based on another set of rows. For example, in a sales and billing system, one might take a customer's order and save the ordered products as rows in an order detail table, and then later generate an invoice based on that order, listing all the ordered products as invoice details.

Consider a scenario such as this, where you want to relate the invoice details to their originating order details, but perhaps a 1:1 relationship is not guaranteed, so you can't simply include the order detail PK in the values inserted into the invoice detail table, and the values that you would need to be able to uniquely identify the originating order detail rows aren't inserted into the invoice detail table. Here's how you would have to do it in ye olde days before the OUTPUT clause:

declare @order_detail table ( product_id int )

insert @order_detail
select ProductID
from OrderDetail
where OrderID = @OrderID

declare @product_id int, @invoice_detail_id int

while exists ( select * from @order_detail )
begin
  select top 1 @product_id = product_id from @order_detail
  
  delete @order_detail where product_id = @product_id
  
  -- Insert a single InvoiceDetail row aggregating one to many OrderDetail rows.
  -- Pretend that for whatever reason, the unique identifying values of the
  -- OrderDetail rows aren't inserted into InvoiceDetail
  insert InvoiceDetail (...)
  select (...)
  from OrderDetail
  where OrderID = @OrderID
  and ProductID = @product_id
  group by OrderID, ProductID
  
  -- Get the generated ID 
  set @invoice_detail_id = scope_identity()
  
  -- Insert correlation rows
  insert OrderDetail_InvoiceDetail ( OrderDetailID, InvoiceDetailID )
  select ID, @invoice_detail_id
  from OrderDetail
  where OrderID = @OrderID
  and ProductID = @product_id
end

This works fine, but it's looping and doing single-row operations in SQL, which is optimized for operating on rowsets. Let's see how the same thing can be accomplished using the OUTPUT clause:

declare @link table ( order_detail_id int, invoice_detail_id int )

insert InvoiceDetail (...)
output OrderDetail.ID, inserted.ID
into @link
select (...)
from OrderDetail
where OrderID = @OrderID
group by OrderID, ProductID

insert OrderDetail_InvoiceDetail
select order_detail_id, invoice_detail_id
from @link

By using the OUTPUT clause, you can capture values from the source table(s) that don't end up getting inserted in the target table along with the generated identity value, and then simply insert the output values into a correlation table. The resulting code is both faster because it's set-based, and shorter, too!