Claúsula Output en Insert

Datos y demás | Sql server

¿Como obtener mensajes de salida que nos devuelvan información sobre, lo que se ha insertado en nuestra tabla?

Además también podemos utilizar este recurso para generar un log sin un trigger.

Imagen de Gerd Altmann en Pixabay

Definición

Devuelve información de las filas afectadas por una instrucción INSERT, UPDATE, DELETE o MERGE, o expresiones basadas en esas filas. Estos resultados se pueden devolver a la aplicación de procesamiento para que los utilice en mensajes de confirmación, archivado y otros requisitos similares de una aplicación.

https://docs.microsoft.com/es-es/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

Exactamente la misma que aplica a update o delete.

Pero hoy vamos a darle un enfoque un pelín diferente.

Escenario

-- Code 1.0

Create table dbo.InsertOutput
(
id smallint identity(1,1) primary key,
nombre varchar(25) not null,
apellido1 varchar(25) not null,
apellido2 varchar(25) not null,
nombreCompleto as 
	Concat(apellido1,' ',apellido2,',',nombre) persisted,
telefono varchar(100) null, 
apellidos as Concat(apellido1, ' ', apellido2)
);
go

Ahora tenemos una tabla, con una serie de columnas. La primera con una especificación de identidad. Y nombre completo es una columna persistida. Y la columna apellidos es una columna calculada. Esto hace del escenario que sea un poco «especial». Ya que ambas columnas se calculan al momento de inserción.

Una sentencia normal de inserción sería tal que:

-- Code 1.1

Insert into dbo.InsertOutput 
(nombre, apellido1, apellido2, telefono)
values
('Ana', 'Pérez', 'Ruiz', '10000');

Y la recuperación de datos de la fila insertada:

-- Code 1.2

Select InsertOutput.id
	 , InsertOutput.nombre
	 , InsertOutput.apellido1
	 , InsertOutput.apellido2
	 , InsertOutput.nombreCompleto
	 , InsertOutput.telefono
	 , InsertOutput.apellidos
	   from dbo.InsertOutput;

Por tanto la columna de identidad se ha generado, y las columnas, tanto persistida, como calculada (persisted y computed), también.

Entonces vamos a aplicar primero la claúsula output en la inserción de una fila para ver que nos devuelve.

-- Code 1.3

Insert into dbo.InsertOutput 
(nombre, apellido1, apellido2, telefono)
output inserted.*
values
('Bea', 'Martín', 'Sol', '10001');

Geníal, tenemos todas las columnas que conforman la fila a nuestra disposición. Si has ejecutado el ejemplo, además observarás que realmente nos las ha retornado a la salida de nuestra query, en la herramienta cliente, que estemos utilizando.

Si podemos recuperar los valores, insertarlos en una variable tipo tabla, también podemos en una tabla temporal, ¿porque no insertarlos directamente en una tabla de log?

Ahora vamos a crear una tabla de log, para anotar todo lo que ocurra en la tabla original. Escenario típico que a veces realizamos con un trigger, o con un versionado de tabla.

-- Code 2.0

Create table dbo.InsertOutputLog (
  nombre varchar(100)
, apellido1 varchar(100)
, apellido2 varchar(100)
, nombreCompleto varchar(200)
, telefono varchar(100) NULL
, apellidos varchar(200)
, accion varchar(25)
);

Las columnas más o menos las mismas que en la tabla original, pero le añadimos una más. Acción que registrará en este caso, el literal «insert».

Para formar los valores a insertar, montamos esta query utilizando un array en sql.

-- Code 2.1
-- Es solo una parte de la próxima insert

  Select t.nombre, t.apellido1, t.apellido1, t.telefono 
    from (
        values 
    ('Cris', 'Pérez','Sal','10002'),
    ('Enma', 'Ramos','García','10003'),
    ('Paty', 'Sobrado','Rama','10004')
    )T(nombre, apellido1, apellido2, telefono)

Esos son los nombres que vamos a insertar, en la sentencia de insertOutput y a la vez en el mismo paquete en insertOutputLog.

-- Code 2.2

Insert into dbo.InsertOutput 
    ( nombre
	, apellido1
	, apellido2
	, telefono)
    output inserted.*
	, 'Nuevo registro' 
	into dbo.InsertOutputLog
Select t.nombre
	 , t.apellido1
	 , t.apellido1
	 , t.telefono 
from (
    values 
('Cris', 'Pérez','Sal','10002'),
('Enma', 'Ramos','García','10003'),
('Paty', 'Sobrado','Rama','10004')
)T(nombre, apellido1, apellido2, telefono);

Y ya tenemos nuestro log insertado.

También funciona con values:

-- Code 2.3

Insert into dbo.InsertOutput 
    ( nombre
	, apellido1
	, apellido2
	, telefono)
    output inserted.*
	, 'Nuevo registro' 
	into dbo.InsertOutputLog
    values 
('Gemma', 'Pér','Seta','10005'),
('Sol', 'Bui','García','10006'),
('Aisa', 'Roda','Clas','10007');

Y por supuesto, además se le puede dar salida en la parte cliente.

-- Code 2.4

Insert into dbo.InsertOutput 
    ( nombre
	, apellido1
	, apellido2
	, telefono)
    output inserted.*
	, 'Nuevo registro' 
	into dbo.InsertOutputLog
    output inserted.*
    values 
('Fedra', 'Dos','Sachez','10006');

Otra alternativa más para nuestras querys.

🙂