OUTPUT en SQL Server

A partir de la version 2005 de SQL Server se introduce la sentencia OUTPUT, mediante la cual se puede ver las filas afectadas por una sentencia INSERT, UPDATE ,DELETE o MERGE individualmente como si fuera un SELECT de los registros afectados o insertarlos directamente en alguna tabla de auditoría por ejemplo.

Su sintaxis según la documentación de MSDN es la siguiente:

 ::=
{
[ OUTPUT  INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT  ]
}
 ::=
{  | scalar_expression } [ [AS] column_alias_identifier ]
[ ,...n ]

 ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $action

para complementar algo el post y que no sea una vil copia de la documentación MSDN, Elaboraremos un ejemplo mediante una pequeña tabla con la siguiente estructura:

create table pruebaOutput (
 ID int not null identity(1,1) primary key, 
 valorA varchar(100) not null, 
 valorB varchar(100) not null )

y otra tabla pequeña que ocuparemos como tabla de auditoría

create table auditoriaOutput ( 
 ID int not null identity(1,1) primary key, 
 ID_Old int,
 ID_New int,
 valorA_Old varchar(100), 
 valorA_New varchar(100) ,
 valorB_Old varchar(100), 
 valorB_New varchar(100) )

Considerando que no tenemos datos en ninguna de las tablas creadas, partamos utilizando OUTPUT dentro de un INSERT

Insert into pruebaOutput (valorA, valorB) 
OUTPUT inserted.ID, inserted.valorA, inserted.valorB
values 
( 'Primer Valor A', 'Primer Valor B' ),
( 'Segundo Valor A', 'Segundo Valor B' ),
( 'Tercer Valor A', 'Tercer Valor B' ),
( 'Cuarto Valor A', 'Cuarto Valor B' ),
( 'Quinto Valor A', 'Quinto Valor B' ),
( 'Sexto Valor A', 'Sexto Valor B' ),
( 'Septimo Valor A', 'Septimo Valor B' ),
( 'Octavo Valor A', 'Octavo Valor B' )

resultado de la clausula output en el insert

Como utilizamos la clausula OUTPUT dentro del insert este insert nos retornará la lista de valores insertados, notese que para este caso utilizamos la palabra clave "inserted" de la sentencia, esta nos permite retornar los nuevos valores o los valores insertados para las columnas especificadas.
Ahora utilizando output en un delete

delete from pruebaOutput 
OUTPUT deleted.ID, deleted.valorA, deleted.valorB
where valorA like '%quinto%' 

Esta al igual que en el ejemplo anterior nos retornara las filas afectadas por la sentencia delete, de acuerdo a las columnas que le especifiquemos en el OUTPUT, en este caso como estamos borrando valores debemos usar la palabra clave "deleted" para obtener los valores borrados
Ahora vamos con un ejemplo para un update, en mi caso tuve que poner los ID desde el 12 al 15 porque estuve jugando un rato antes con esto y se incremento el valor para asignar del identity.

update pruebaOutput set 
 valorA = valorA + 'mas un extra'
   ,valorB = valorA + '(ahora el valor B es el valor A)'
OUTPUT inserted.ID, deleted.ID, inserted.valorA, inserted.valorB, deleted.valorA, deleted.valorB  
where ID between 12 and 15 

lo que nos devuelve es esto

en este caso lo que utilizamos dentro del output las keywords "inserted" y "deleted", de esta forma en nuestro comando UPDATE, las columnas prefijadas por "inserted" corresponden a los nuevos valores y las prefijadas por "deleted" a los reemplazados.
Por último les muestro como utilizar esta sentencia para apoyar la auditoria de registros, por ejemplo cuando corremos algun script medio truculento

--actualizamos algunos valores de la tabla 
update pruebaOutput set 
 valorB = valorB + '(cambiado para insertar en la tabla de auditoria)'
OUTPUT deleted.ID, inserted.ID
     , deleted.valorA, inserted.valorA
     , deleted.valorB, inserted.valorB  
INTO auditoriaOutput
where ID between 15 and 18      

--insertamos nuevos registros en la tabla
Insert into pruebaOutput (valorA, valorB) 
OUTPUT null, inserted.ID
     , null, inserted.valorA
     , null, inserted.valorB
INTO auditoriaOutput     
values 
( 'otro valor mas para A', 'para B tambien' ),
( 'no soy mas creativo para esto', 'es lo que hay no mas' )

--borramos toda la tabla no mas
delete from pruebaOutput
OUTPUT deleted.ID, null
     , deleted.valorA, null
     , deleted.valorB, null
INTO auditoriaOutput     

Al ejecutar todo junto y como estamos insertando en una tabla veremos los mensajes de filas afectadas, pero si consultamos los valores de la tabla veremos lo que ha pasado.

select * from auditoriaOutput

Usar el output sobre el comando MERGE, es lo mismo, pero como no se bien como utilizar merge lo dejo pendiente para cuando aprenda