Hi,
Today let's discuss about OUTPUT command introduced in SQL 2005. Also we will see how MERGE statement use OUTPUT command.
SCENARIO
We need to audit the changes done on a table and there is a catch that this table is being modified from several applications but we need to audit only specific fields.
SOLUTION
Use Triggers, write it on the table and whenever there is any update, we check for the fields that are required for Audit and if any of those fields are updated, insert data in the Audit table.
QUESTION
In this case, what will happen if one of the user is updating comments fields, which is not required for Auditing and he has to update approx 1 million records.
SOLUTION
Now Triggers won't be efficient because this user updates 1 million records. Moreover he is updating comments field only, which is not required for Audit, the result will be poor performance.
In this case
OUTPUT command will be the better solution because we can add
OUTPUT clause.
As per BOL, The OUTPUT clause is not supported in the following statements:
- DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
- INSERT statements that contain an EXECUTE statement.
- Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
- The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
- A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
Let's take a look at examples of OUTPUT clause:
Create table test
(a int identity,
b varchar(100))
insert into test
output inserted.*
select 'Simple Test'
Output:
A simple delete example:
delete test
output deleted.*
Output:
A simple Update example:
update test
Set b='Update Tested'
Output Inserted.*, deleted.*
Output
Now let's insert data in a permanent table, so that we can audit the changes.
Create table Orders
(OrderId int identity,
CustomerName varchar(100))
Create table Orders_Adt
(OrderId int,
OldCustomerName varchar(100),
NewCustomerName varchar(100),
RecType char(1))
insert into Orders
output inserted.OrderId, null, inserted.CustomerName,'I' into Orders_Adt
values('Output Clause')
select * from Orders
select * from Orders_Adt
Output:
Let's update the customer name and store data in the Audit table:
update Orders
Set CustomerName='Updated Customer'
Output inserted.OrderId, deleted.CustomerName, inserted.CustomerName, 'U' into Orders_Adt
Where OrderId=1
select * from Orders
select * from Orders_Adt
Output:
Same way we can use Delete.
Delete Orders
Output deleted.OrderId, deleted.CustomerName,null,'D' into Orders_Adt
select * from Orders
select * from Orders_Adt
Output:
Now let's see how OUTPUT can be used with MERGE command:
Merge performs insert, update, or delete operations on a target table based on the results of a join with a source table.It helps synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
Use AdventureWorks database for following example:
TARGET TABLE = Production.ProductInventory
SOURCE TABLES = Sales.SalesOrderHeader, Sales.SalesOrderDetail
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO
--In this example we are updating data based on date parameter passed to the procedure.
EXECUTE Production.usp_UpdateInventory '20030501'
This example basically matches two tables (Source and Target),
when the data matches and
target table (Production.ProductInventory) quantity - source table quantity <=0
then delete the record from TARGET table.
When the data matches then
update target table quantity with target table quantity - source table order quantity.
At the end we are using OUTPUT command "
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,Deleted.Quantity, Deleted.ModifiedDate;" to get output.
$action indicate the type of operation (INSERT / UPDATE / DELETE)
This way we can use OUTPUT command and overcome some problems with Trigger.
Hope this will be helpful. If you have any query/concern, write to me.
HAPPY SQL CODING.
