La cláusula OUTPUT se introdujo en la versión de SQL Server 2005. La cláusula OUTPUT devuelve los valores de cada fila afectada por las instrucciones INSERT, UPDATE o DELETE. Incluso admite la instrucción MERGE, que se introdujo en SQL Server 2008. El resultado de la cláusula OUTPUT se puede insertar en una tabla separada durante la ejecución de la consulta. Esta cláusula se utiliza más comúnmente con fines de auditoría. Al usar esto, alguien puede implementar un proceso personalizado que funcione como CDC, o actuar de la misma manera que CDC.

Además, los resultados de la cláusula OUTPUT se pueden devolver a las aplicaciones de procesamiento para su uso en cosas como mensajes de confirmación, registros y cualquier otro requisito de la aplicación. En este artículo, proporcionaré un conjunto de ejemplos para mostrar el uso de la cláusula OUTPUT al capturar los resultados de las filas actualizadas en una variable de tabla para las instrucciones UPDATE.

Primeros pasos

Para seguir los siguientes ejemplos, deberá crear una tabla Department_SRC ejecutando el siguiente código T-SQL en una base de datos de SQL Server. La sección a la que se hace referencia contiene Output_Update.archivo sql, que incluye el código T-SQL para probar los siguientes ejemplos.

IF OBJECT_ID ('Department_SRC', 'U') IS NOT NULL DROP TABLE dbo.Department_SRC;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NOT NULL, varchar(50) NOT NULL, NOT NULL) ON Insert into .(,,) Values('Engineering','Research and Development',getdate());

Puede ver en los datos de la tabla que el registro se ha insertado en la tabla como se muestra en la imagen de abajo.

Implemente la cláusula OUTPUT en Instrucciones de actualización

Como comentamos en mi artículo anterior, la cláusula OUTPUT da acceso a dos tablas virtuales (Tablas Mágicas):

1. «INSERTED» contiene la nueva filas (CONJUNTO de INSERT o UPDATE)
2. «DELETED» contiene la copia antigua de las filas(CONJUNTO de UPDATE)

La cláusula OUTPUT con una instrucción UPDATE tendrá acceso a las tablas internas INSERTADAS y ELIMINADAS. Esto significa que ambas tablas son accesibles al mismo tiempo que se ejecuta la instrucción UPDATE. Por ejemplo, supongamos que estamos actualizando algunas columnas de una tabla para una sola fila. Los valores recién actualizados son los cambios en la tabla y se encuentran en la tabla interna insertada. Además, los datos que se han cambiado son los valores de datos antiguos y se insertan en la tabla interna ELIMINADA.

Visualización de valores de actualización

Utilizando la cláusula OUTPUT, podemos mostrar los valores actualizados en la ventana de salida seleccionando los nombres de columna con el prefijo INSERTADO o utilizando INSERT.* para mostrar todas las columnas de la tabla INSERTADA. Además, podemos mostrar los valores de datos antiguos de la tabla en la ventana de salida seleccionando los nombres de columna con el prefijo ELIMINADO o utilizando ELIMINADO.* para mostrar todas las columnas de esa tabla. Esto también ayudará a imprimir algunos de los mensajes de salida en la ventana de management studio.

Para explicar esto con un ejemplo, estoy ejecutando el siguiente código, en el que estamos tratando de actualizar la columna Nombre de grupo del valor «Investigación y Desarrollo» al valor «TI» para el departamento de «Ingeniería».

--Update the GroupName for the Department.Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.Name ,deleted.GroupName as GroupName_old, deleted.ModifiedDate as ModifiedDate_old, inserted.GroupName as GroupName_new, inserted.ModifiedDate as ModifiedDate_newWhere ='Engineering'

Una vez que ejecutamos el código anterior, podemos ver el resultado de la cláusula de SALIDA como se muestra en la imagen de abajo.

Insertar los resultados de SALIDA en una variable de tabla

Para mostrar este ejemplo, declaré una variable de tabla llamada @Updated para almacenar los resultados de la cláusula de SALIDA en esta variable de tabla. Usamos la SALIDA EN sintaxis para almacenar los resultados en una variable de tabla. En este enfoque, tenemos acceso a establecer filas, tanto los valores nuevos como los valores antiguos que han sido actualizados por la consulta, que se pueden usar para los siguientes pasos en el mismo lote.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted.GroupName as GroupName_old, inserted.GroupName as GroupName_new, deleted.ModifiedDate as ModifiedDate_old,inserted.ModifiedDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Querying from @Updated output tableSelect * from @Updated

Una vez que ejecutamos el código anterior, podemos ver que los resultados se han insertado en la variable de tabla @Updated como se muestra en la imagen de abajo.

Uso de la cláusula OUTPUT para cargar una Dimensión de TIPO 3 que cambia lentamente

Con los ejemplos mostrados anteriormente, la cláusula OUTPUT es una muy buena alternativa para cargar una tabla de dimensiones de TIPO 3. Para explicar esto con un ejemplo, estoy ejecutando el siguiente código, que creará una tabla de dimensión de TIPO 3 llamada Department_Type3.

---Type 3 table exampleIF OBJECT_ID ('Department_Type3', 'U') IS NOT NULL DROP TABLE dbo.Department_Type3;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NULL, varchar(50) NULL, varchar(50) NULL, NULL) ON GO---Insert some test valuesInsert into .(,,) Values('Engineering','Research and Development',getdate());Select * from .

Una vez que ejecutamos el código anterior, podemos ver el resultado de los datos de la tabla como se muestra en la imagen de abajo.

Mientras estamos insertando un nuevo registro en la tabla Department_Type3, podemos ver que la columna GroupName_old tiene un valor NULO y GroupName_Current tiene el valor de la inserción reciente. Supongamos que hubo un cambio en el nombre de grupo_current para el Nombre del Departamento de» Ingeniería » y necesitamos actualizar la tabla Department_Type3 con el nuevo valor de nombre de grupo_current. Dado que se trata de una tabla de dimensiones TYPE3, el requisito aquí es actualizar la columna GroupName_old con «Investigación y desarrollo» y GroupName_Current con el nuevo valor.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', EffectiveDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted. as ,inserted.GroupName_current as GroupName_new, deleted.EffectiveDate as ModifiedDate_old,inserted.EffectiveDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Update the GroupName_old with old valuesUpdate aSet a.GroupName_old=b.GroupName_old, a.EffectiveDate=b.ModifiedDate_newfrom as ainner join @Updated as bon a.DepartmentID=b.DepartmentID--Querying the final tableSelect * from 

Una vez que ejecutemos el código anterior, capturaremos los resultados de la tabla interna ELIMINADA en la variable @Updated table y luego usaremos esos datos para actualizar las columnas de la tabla final para implementar esta dimensión de cambio lento de TIPO 3. Podemos ver el resultado de los datos de la tabla final antes y después de la ejecución de la instrucción anterior, como se muestra en las imágenes a continuación.

Antes de la ejecución de la instrucción de actualización

Después de la ejecución de la instrucción de actualización

Limitaciones de la cláusula de salida

Instrucciones DML que hacen referencia a vistas con particiones locales, vistas con particiones distribuidas o tablas remotas.

La lista completa se muestra en este enlace: https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

Resumen

Como se muestra en los ejemplos anteriores, podemos ver que la cláusula OUTPUT es fácil de usar y puede evitar muchos códigos personalizados para capturar los resultados de salida de valores actualizados con valores antiguos y nuevos en una variable de tabla durante la ejecución de la consulta.