Capitulo 2 – Lección 2: OUTPUT y MERGE

Puede utilizar la cláusula OUTPUT y MERGE para mejorar la funcionalidad proporcionada por las instrucciones DML. La cláusula OUTPUT permite devolver la información de filas afectadas por una instrucción INSERT, UPDATE, o DELETE.Con esta funcionalidad, puede realizar tareas adicionales de forma más limpia basada en la información proporcionada. Estas tareas pueden incluir la confirmación de e-mails, datos de la auditoría, o tareas similares.
La instrucción MERGE le ofrece la posibilidad de realizar un INSERT, UPDATE, o DELETE en una tabla de destino sobre la base de un conjunto de reglas que son determinadas por una comparación entre la fila de tabla de destino y una tabla de origen.

Uso de la cláusula OUTPUT (Para más información clic AQUI)


La cláusula OUTPUT ofrece la posibilidad de acceder a las tablas insertadas y eliminadas que en versiones anteriores a SQL Server 2005 sólo eran accesibles a través de disparadores (triggers). Debido a esto, algunas de las funciones que se realizaron previamente a través de disparadores pueden ser manejados por los procedimientos almacenados en su lugar, eliminando la necesidad de ciertos factores desencadenantes.
La sintaxis específica de la cláusula OUTPUT es la siguiente:

image

En muchos entornos existe la necesidad de registrar los datos insertados una tabla de auditoría. En el ejemplo siguiente, la empresa quiere ser capaz de generar informes para identificar el número de filas se insertan en distintos períodos de tiempo. Entre los requisitos piden que se especifiquen la fecha de la fila se inserta, junto con el identificador de clave principal de la tabla de origen, en  una tabla auditoría especial que se utiliza para ejecutar estos informes. Una forma de lograr esto es con la cláusula OUTPUT. Para este ejemplo, la tabla de auditoría incluye una columna de AuditID, que es una columna de identidad, una columna de InsertedDate, que contiene la fecha de la fila se inserta, y la columna InsertedID, que contiene el valor de clave principal de la fila insertada en la tabla que se auditados.El siguiente comando agrega una nueva fila a la tabla de empleados, y también agrega una fila correspondiente a la fecha y hora actual y la EmployeeID para el empleado en la tabla de auditoría:


INSERT INTO Employees
(LastName, FirstName, Title)
OUTPUT getdate(), inserted.EmployeeID INTO Audit
VALUES ('Ralls', 'Kim', 'Support Rep');

Otro escenario implica el archivo de datos. Por ejemplo, supongamos que una empresa quiere mover todos los registros de pedidos realizados antes de 01 de diciembre 1997, de la tabla Order Details a la mesa de OrderDetailsArchive. Puede eliminar las filas de la tabla Order Details y pasar a la tabla de OrderDetailsArchive en un solo paso mediante el uso de la siguiente instrucción DELETE con la cláusula OUTPUT:
DELETE FROM [Order Details]
OUTPUT deleted.* INTO OrderDetailsArchive
FROM Orders join [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE OrderDate < '12-01-1997';



El último escenario es para una empresa que le gustaría ver el estado antes y después de la columna CategoryName cuando se realizan actualizaciones de esta columna. Esta información, junto con la fecha de modificación y el ID de inicio de sesión para el empleado que los han modificado, se reunieron en una tabla de nombre CategoryChanges El siguiente comando agrega la información a una tablaCategoryChanges que incluye las siguientes columnas: ChangeID, CategoryID,OldCategoryName, NewCategoryName, ModifiedDate, y LoginID. El comando también modifica a la tabla CategoryName y añade una fila a la tabla CategoryChanges:

UPDATE Categories
SET CategoryName = 'Dried Produce'
OUTPUT inserted.CategoryID, deleted.CategoryName
, inserted.CategoryName, getdate(), SUSER_SNAME()
INTO CategoryChanges
WHERE CategoryID = 7;


Uso de la cláusula MERGE (Para más información clic AQUI)
La instrucción MERGE, junto con la captura de datos (CDC), fueron introducidas en SQL Server 2008, para mejorar la funcionalidad de los data warehouses y bases de datos provisionales (staging databases).
La instrucción MERGE le da la capacidad para comparar filas de una fuente y
tabla de destino. A continuación, puede definir el INSERT apropiado, UPDATE, o DELETE que se realiza en base a los resultados de la comparación.
La sintaxis específica de la cláusula MERGE es la siguiente:

image

La explicación de cada uno de los comandos podrán encontrarlas AQUI.

Un escenario común es combinar el movimiento de datos de una tabla a otra. Por ejemplo, supongamos que una empresa necesita para copiar la información de la tabla SalesOrderDetail en la base de datos AdventureWorks2008 en la tablaSalesOrderDetailHistory.
La tabla SalesOrderDetailHistory incluye una columna llamada Cancelled , además de las columnas definidas en la tabla SalesOrderDetail. En lugar de utilizar timestamps o algún otro método para identificar las filas que han cambiado desde la última vez que la información ha cambiado, la instrucción MERGE puede comparar dos tablas e insertar sólo las filas nuevas, en lugar de tener que insertar todas las filas o columnas. El uso de CDC hace que este proceso sea aún más eficaz.
La instrucción MERGE inserta aquí las filas nuevas en la tablaSalesOrderDetailHistory y añade un valor "True" a la columna de cancelados de las filas que ya no existen en la tabla de origen.

MERGE INTO Sales.SalesOrderDetailHistory AS SODH
USING Sales.SalesOrderDetail AS SOD
ON SODH.salesorderid = SOD.salesorderid
AND SODH.SalesOrderDetailID = SOD.SalesOrderDetailID
WHEN NOT MATCHED BY TARGET THEN
INSERT (Linetotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, Cancelled)
VALUES (Linetotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate,DEFAULT)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET SODH.Cancelled = 'True';


Puede utilizar la cláusula OUTPUT en conjunto con la variable $action para proporcionar información sobre si las inserciones, actualizaciones o eliminaciones se realizan durante la ejecución de la instrucción MERGE, como se muestra en la siguiente imagen:

image


Resumen de la lección
  • La cláusula OUTPUT permite redirigir la información de la aplicación que llame, o a un objeto como una tabla o una variable de tabla, sobre la declaración INSERT, UPDATE, o DELETE.
  • La instrucción MERGE permite llevar a cabo acciones de DML en una tabla de destino en función de si o no coincide con la información fila en una tabla de origen.

No hay comentarios:

Publicar un comentario