Using INSERT OUTPUT in a SQL Server Transaction
By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL
Free MSSQLTips Webinar: Development Best Practices for SQL Server
Attend this webinar to learn about development best practices for SQL Server. Andy Warren compartirá sus muchos años de experiencia para dar algunos consejos sobre lo que le ha funcionado mejor y cómo puede utilizar parte de este conocimiento.
Problema
Con frecuencia me encuentro en situaciones en las que necesito insertar registros en una tabla en una operación basada en conjuntos envuelta dentro de una transacción donde, de forma secundaria y dentro de la misma transacción, genero inserciones posteriores en tablas relacionadas donde necesito pasar valores de clave que fueron el resultado del comando de INSERCIÓN inicial. Gracias a una mejora de Transact/SQL en SQL Server, esto se hizo mucho más fácil y se puede hacer en una sola instrucción… ¡SIN GATILLO!
Solución
Una de las mejoras de Transact/SQL en Microsoft SQL Server es la subcláusula de SALIDA de la instrucción INSERT. Ahora puede capturar los registros insertados a través de una instrucción INSERT (piense que también puede capturar valores de columna de identidad para las filas nuevas) para su uso posterior en una instrucción INSERT adicional para que una tabla secundaria conserve la integridad referencial sin necesidad de un desencadenador INSERT.
¿Por qué no usar un disparador? Es una construcción viable y probada de SQL Server, ¿verdad?
La respuesta corta es » Sí, lo es.»Sin embargo, los disparadores son uno de esos pequeños secretos desagradables que guarda la base de datos. No saltan directamente hacia ti y dicen » ¡AQUÍ ESTOY!»Tomemos, por ejemplo, el proceso de solución de problemas de bloqueos o el ajuste de una consulta de mal rendimiento: un disparador sentado en segundo plano que funciona como se le ha pedido puede estar causando sus problemas, pero pasará por muchas iteraciones de procedimientos almacenados de búsqueda y código T/SQL ad hoc antes de que probablemente incluso se detenga para considerar que hay un disparador que dispara comandos de lenguaje de modificación de datos (DML): INSERCIONES, ACTUALIZACIONES o ELIMINACIONES que son adjuntas a lo que está tratando de diagnosticar. Asocio el uso de disparadores con el uso de código T/SQL ad hoc utilizado en la pila de código de una aplicación y pasado a una instancia de SQL Server para evitar prácticas de procesamiento.
Es por eso que me gusta lo que veo con la construcción INSERT-OUTPUT. Obtiene los beneficios de poder capturar los valores insertados que luego puede pasar a un comando secundario, y puede envolver todo esto dentro de una sola transacción para atomicity. La sintaxis para esta construcción se muestra a continuación y difiere solo ligeramente del comando básico INSERT T / SQL:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --y otras columnas de SOME_TABLE si es necesario
EN SOME_OTHER_TABLE>
(
column_list>
)
SELECCIONE
(
column_list>
)
DESDE source_table_OR_JOIN_of_multiple_tables>
DONDE filtering_criteria>
La única diferencia entre esto y un estándar de instrucción INSERT es la inclusión en la SALIDA…EN declaración. Para facilitar esto, piense en ello como simplemente una instrucción de inserción secundaria dentro de la instrucción de inserción original que captura los valores de la tabla INSERTADA virtualizada, la misma tabla que usaría un disparador, para procesar una INSERCIÓN secundaria en otra tabla. En el siguiente ejemplo, y de acuerdo con la temporada de fiestas, digamos que usted es responsable de hacer un poco de contratación en las oficinas corporativas de AdventureWorks. Un viejo elfo alegre está siendo contratado para algunas promociones en la tienda y, de acuerdo con la política corporativa, siempre realiza una revisión de 90 días para cualquier nueva contratación. Queremos que se registre la noticación cuando se ingrese el nuevo empleado sin ningún trabajo adicional por parte de los Recursos Humanos. El siguiente código demuestra cómo podemos usar INSERT-OUTPUT para hacer esto.
USE AdventureWorks;
GO
- - - Crear tablas de ejemplo
/*
Nota, esto no está completamente normalizado. Habría incluido otra tabla
para los Tipos de notificación si esta fuera una solución real.También usaría una columna int NotificationTypeID en la tabla de notificaciones en lugar de una columna varchar (xx) NotificationType.
* /
CREAR dbo DE AUTORIZACIÓN DE ESQUEMA;
IR
CREAR TABLA .
(
IDENTITY(1,1) not NULL,
VARCHAR(30) not NULL,
VARCHAR(30) not NULL,
RESTRICCIÓN PRIMARY KEY CLUSTERED
(
ASC
)EN
) EN ;
CREAR TABLA .
(
IDENTITY(1,1) NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR (30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
) ON; Ahora que hemos construido los objetos para este pequeño ejercicio, podemos ver la construcción INSERT-OUTPUT en acción…
/ *
Demuestre cómo puede insertar los valores clave agregados al Pentagrama.StaffID
en las notificaciones.StaffID en una sola transacción
*/
INSERTAR EN HR. Staff (Nombre, apellido)
SALIDA INSERTADA.StaffID, DATEADD(d,90,GETDATE()),'Revisión de 90 días'
EN HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALORES ( 'Santa','Claus'); Seleccionando ahora de las tablas Staff y Notification, verá que los valores clave se ingresaron correctamente en ambas tablas:
SELECCIONE * DE HR.Staff;
SELECCIONE * DE HR.Notification; 
Ahora hay una advertencia muy importante y bastante limitante al uso de INSERT-OUTPUT. El objetivo de salida no puede formar parte de ninguna relación de clave externa. Incluso si no hay una relación en cascada con ningún otro objeto a través de esa relación en la base de datos. Veamos qué pasa si lo es. Agregaremos una clave foránea a la notificación en StaffID, haciendo referencia a la columna StaffID en la tabla Staff y luego intentaremos agregar ayuda adicional de vacaciones:
Add Agregar clave foránea para la columna StaffID a la tabla de notificaciones
ALTER TABLE HR. Notification ADD CONSTRAINT
CLAVE FORÁNEA
(
StaffID
)
REFERENCIAS HR.Staff
(
StaffID
);
/*
Demuestre cómo puede insertar los valores de clave agregados al Pentagrama.StaffID
en las notificaciones.StaffID en una sola transacción
*/
INSERTAR EN HR. Staff (Nombre, apellido)
SALIDA INSERTADA.StaffID, DATEADD (d,90,GETDATE ()),'Revisión de 90 días'EN HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALORES ('Frosty', 'Snowman');
SELECCIONAR * DESDE HR. Staff;
SELECCIONAR * DESDE HR.Notification;
El siguiente mensaje de error se devuelve como se esperaba:
Msg 332, NIVEL 16, Estado 1, Línea 17
La TABLA de destino 'HR.Notification' DE la cláusula OUTPUT INTO no puede estar EN ninguno DE los lados DE una relación (CLAVE PRIMARIA, CLAVE FORÁNEA). RESTRICCIÓN de referencia encontrada 'FK_Notification_Staff'. Esto es probablemente bueno en este caso, ya que es muy probable que el Sr. Muñeco de nieve no esté presente en 90 días.
Pasos siguientes
- Lea más sobre la cláusula de salida
- También lea este consejo anterior sobre la cláusula de salida
- Más consejos del autor están disponibles a través de este enlace.
Última actualización: 2010-12-13
Sobre el autor
Tim Ford es un Senior Administrador de Base de datos con MindBody.Ver todos mis consejos - Más Consejos para Desarrolladores de bases de datos…