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

Obtener las Relaciones FK de una tabla

Desde el siguiente enlace encontré como obtener las FK de todas las tablas de una base de datos, realizándole un mínimo cambio lo ajusté para que obtuviese solo las FK's de alguna tabla en particular

declare @tabla as varchar(1000) 
set @tabla = 'nombre_tabla'

SELECT  so.name as ForeignTableName, 
  sc.name  as ColumnName, 
  s.name + '.' + PKT.name as PrimaryTableName
from sysreferences r
inner join  sysconstraints c on r.constid = c.constid
inner join sys.tables pkt on PKT.object_id = r.rkeyid
inner join sys.columns sc on sc.column_id = r.fkey1 and r.fkeyid = sc.object_id 
inner join sys.schemas s on s.schema_id=pkt.schema_id
inner join sys.tables so on r.fkeyid = so.object_id
where so.is_ms_shipped = 0 
  and ( pkt.object_id = object_id(@tabla) or r.rkeyid = object_id(@tabla))
order by so.name, sc.name

esto generaría un resultado como el siguiente

Obtener las columnas de una tabla en SQL Server 2008

Aquí les dejo un script con el cual podemos obtener la estructura de una tabla dada, especifícada en la variable @tabla, identificando las columnas que son PK dentro de la misma. Creo que tambien sirve perfectamente para SQL Server 2005.

declare @tabla as varchar(1000) 
set @tabla = 'gen_intranet_dfncn_prmtr'

select OBJECT_NAME(OBJECT_ID(@tabla)) tabla,
 col.name as nombre, 
 ( select name from sys.types tip where col.system_type_id = tip.system_type_id ) as tipo, 
 col.max_length as longitud,
 col.precision as precisión,
 col.scale as escala,
 case when col.is_nullable = 0 then 'NOT NULL' else 'NULL' end as nullable,
 case when col.is_identity = 0 then '' else 'SI' end as [es identity],
 case when icol.column_id is not null then 'PK' else '' end as [es pk]
from sys.columns col 
 left join ( select icol.* 
    from sys.index_columns icol 
     inner join sys.indexes idx on icol.index_id = idx.index_id 
    where idx.is_primary_key = 1
      and idx.object_id = object_id(@tabla)) as icol on col.column_id = icol.column_id and col.object_id = icol.object_id
where col.object_id = object_id(@tabla)
order by col.column_id

El resultado que nos queda sería algo como esto:

Convertir TimeSpan .Net a DateTime de SQL Server

El tipo de datos TimeSpan de .Net generalmente se mapea a SQL Server en forma de un long o big int, lo cual está correcto ya que SQL Server no posee un tipo de datos adhoc para representar el TimeSpan (por lo menos en 2005).
El problema surge cuando queremos convertir este los ticks guardados en base de datos a un DateTime.
Resulta que los Ticks son la representación en nanosegundos (1 paso del tick corresponde a 100 nanosegundos) de un intervalo de tiempo dado, por lo tanto si quisieramos convertir a un datetime estos valores solo debieramos agregarle a una fecha X el valor de los ticks que queremos para representar la hora. Como en SQL Server 2005, la funcion DATEADD permite solo agregar hasta milisegundos debemos los ticks a milisegundos para utilizarlos y esto lo hacemos dividiendo por 10.000 o multiplicando por 10^-4 esto por que cada tick esta representado en como intervalos de 100 nanosegundos (10² * 10^-9 = 10^-7).

Dejando toda la cháchara de lado el ejemplo que es lo que importa

--queremos representar la fecha 2010-01-07 a las 14:15:00 hrs, es decir, 
--con 513000000000 ticks
SELECT DATEADD(millisecond, 513000000000 / 10000, '2010-01-07')

lo que nos devuelve

2010-01-07 14:15:00.000

Ejecutar Comandos en SQL Server 2000

Algo pequeño, con este comando (xp_cmdshell) pueden ejecutar un comando de MS-DOS desde el servidor de SQL Server

Por ejemplo

exec xp_cmdshell 'dir c:\*.* /B'

devuelve

OUTPUT

.rnd

Archivos de programa

AUTOEXEC.BAT

CONFIG.SYS

Documents and Settings

ea011e2d283b8a4855eb45

Inetpub

lotus

oracle

WINDOWS

NULL

Bienvenida

Como prácticamente no tengo posts, la mayoría irá a leer esto, espero que en el futuro este blog puede aportar tanto a mí, ayudandome a recordar cosas que vaya haciendo. como a otros que puedan encontrar solución a sus problemas en base a mis vivencias como programador...


Saludos