La cláusula de salida de SQL Server devuelve los valores de cada fila afectada por una instrucción INSERT, UPDATE o DELETE.
La cláusula Output devuelve una copia de los datos que se pueden insertar en una tabla separada durante la ejecución de la consulta.
Esta cláusula se usa más comúnmente para realizar un seguimiento de los cambios de datos en la tabla con fines de auditoría.La cláusula de salida
da acceso a dos tablas virtuales como se indica a continuación.
- INSERTADO que contiene las filas nuevas (INSERTAR o ACTUALIZAR)
- ELIMINADO que contiene la copia antigua de las filas (vacía para INSERTAR)
La cláusula OUTPUT puede acceder a todas las columnas de la tabla, incluso aquellas que no forman parte de la instrucción Insert .
SINTAXIS
---- for insertINSERT INTO tablename OUTPUT inserted . *INTO @output_tablenameVALUES (col_value1 ,col_value2 ,..) ---for delete DELETE FROM tablenameOUTPUT deleted.* INTO @output_tablename WHERE Condition
CREATE TABLE dbo.Item (ItemId Int identity(1,1),ItemName Varchar(20),ItemPurchasedDate Datetime)
Uso de la cláusula Output con la instrucción Insert
Para insertar el resultado de la cláusula output en una tabla , declare primero una variable de tabla para almacenar el resultado de la cláusula output y utilice la sintaxis de caluse de salida para almacenar el resultado en la variable de tabla .
Para acceder a los registros insertados, la cláusula Output utiliza la tabla virtual insertada que contiene las filas nuevas.
En la siguiente instrucción, insertamos un registro en la tabla de elementos y usamos la cláusula output para almacenar esos registros en una variable de tabla @insert_log como se indica a continuación
DECLARE @insert_log TABLE(ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )INSERT INTO dbo.Item OUTPUT inserted.ItemId, inserted.ItemName, inserted.ItemPurchasedDateINTO @insert_logVALUES ('Item1' , Getdate()) SELECT * FROM dbo.Item;SELECT * FROM @insert_log ;
Como puede ver la salida de la tabla de elementos y la variable de tabla, al insertar nuevos registros en la tabla de elementos, ItemId no forma parte de la instrucción insert . Sin embargo, puede acceder a la columna ItemId de la cláusula OUTPUT.
Usando Output caluse con instrucción de actualización
Para acceder a los registros actualizados, puede usar una tabla virtual insertada o eliminada con cláusula de salida según sus requisitos.
Si desea acceder a valores antiguos que se encontraban antes de actualizar el nuevo valor, puede usar tabla virtual eliminada y si desea acceder a nuevos valores actualizados, puede usar tabla virtual insertada con cláusula de salida.
En la siguiente instrucción, actualizamos el valor itemName en la tabla de elementos y usamos la cláusula output para almacenar los valores antiguos de esos registros en una variable de tabla @update_log.
Como queremos acceder a registros antiguos, usamos tabla virtual eliminada.
DECLARE @update_log TABLE( ItemId int, ItemName Varchar(20), ItemPurchasedDate Datetime )UPDATE dbo.Item SET ItemName ='Item2'OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDateINTO @update_logWHERE ItemId =1SELECT * FROM dbo.Item;SELECT * FROM @update_log
Como puede ver, aquí tenemos tres de salida de la siguiente manera
1: es la tabla de salida antes de la actualización de los registros.
2: Es la salida de la tabla después de actualizar los registros.
3: Es la salida devuelta por la variable de tabla que contiene los valores de registros antiguos que se encontraban antes de la actualización.
Si desea acceder a nuevos registros, puede usar la tabla virtual insertada en lugar de eliminada.
Cláusula de salida con instrucción Delete
La siguiente instrucción utiliza la consulta delete para eliminar el registro de ItemId = 1 de la tabla de elementos y almacenar los registros eliminados en la variable de tabla mediante caluse de salida .
Para acceder a la cláusula de salida de registros eliminados, utilice la tabla virtual eliminada que contiene la copia antigua de las filas.
DECLARE @delete_log TABLE( ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )DELETE FROM dbo.Item OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDateINTO @delete_logWHERE ItemId =1SELECT * FROM dbo.Item;SELECT * FROM @delete_log
Como puede ver , no hay datos disponibles en la tabla de elementos, ya que se elimina, pero la cláusula de salida inserta los registros eliminados en una variable de tabla @delete_log, que es muy útil para realizar un seguimiento de los registros eliminados.
Recomendado para usted
Tablas temporales de SQL Server
822 vistas totales, 8 vistas hoy