Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

OUTPUT

  • 5 February 2013
  • Author: Kathi Kellenberger
  • Number of views: 3089
  • 0 Comments

The OUTPUT clause, which has been around since SQL Server 2005, is a nifty little feature that lets you save the values of the rows you have just modified. You can just return this information or save it in an auditing table, for example. I’m doing some unit testing on several SSIS packages and have found the OUTPUT feature is invaluable. In my case, I am tweaking a few rows in the existing data to make sure I have rows that go down each possible path in the data flow. In order to keep a record of the data I have manipulated, I am using the OUTPUT clause.

The OUTPUT clause is similar to a trigger in that you use INSERTED and DELETED tables. That means that any rows you insert will show up in INSERTED. Any rows you delete will show up in DELETED. Any rows that you update will show the old values in DELETED and the new values in INSERTED.

Here is an example:

USE AdventureWorks2012;
GO
CREATE TABLE #TempSales(SalesOrderID INT NOT NULL,
	OrderDate DATE NOT NULL, TotalDue MONEY NOT NULL)

INSERT INTO #TempSales(SalesOrderID, OrderDate, TotalDue) 
OUTPUT INSERTED.*
SELECT TOP(5) SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate;

In this example, I am creating a temp table, and then adding 5 rows from Sales.SalesOrderHeader. Notice that the OUTPUT clause is between the INSERT and SELECT parts of the statement. (The trickiest thing about using OUTPUT to me is figuring out where it goes in the statement.) This statement not only inserts the 5 rows into #TempSales, it also returns those rows to me.

The next example shows how I can save those rows into an auditing table.

CREATE TABLE #SalesAudit(SalesOrderID INT NOT NULL, OrderDate DATE NOT NULL, 
	TotalDue MONEY NOT NULL, CreatedDate SMALLDATETIME NOT NULL, CreatedBy VARCHAR(10)NOT NULL);

INSERT INTO #TempSales(SalesOrderID, OrderDate, TotalDue) 
OUTPUT inserted.SalesOrderID, inserted.OrderDate, inserted.TotalDue, 
	SYSDATETIME(), 'KJK'
INTO #SalesAudit
SELECT TOP(5) SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate;

SELECT * FROM #SalesAudit; 

Notice that I can use other expressions, not just the columns found in the INSERTED table.
Another interesting thing about OUTPUT, is that you can display the data and insert into a table at the same time by using an additional OUTPUT clause. This next example shows how to do that:

INSERT INTO #TempSales(SalesOrderID, OrderDate, TotalDue) 
OUTPUT inserted.SalesOrderID, inserted.OrderDate, inserted.TotalDue, 
	SYSDATETIME(), 'KJK'
INTO #SalesAudit
OUTPUT inserted.SalesOrderID, inserted.OrderDate, Inserted.TotalDue
SELECT TOP(5) SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate;

You can also capture data from an update. Here is an update example:

UPDATE #TempSales 
SET OrderDate = DATEADD(m,1,OrderDate) 
OUTPUT deleted.SalesOrderID, deleted.OrderDate AS OldDate, 
	inserted.OrderDate AS NewDate;


 

Notice that the orginal value is returned from DELETED and the new value is returned from INSERTED. You can capture deleted rows as well:

DELETE #TempSales
OUTPUT deleted.SalesOrderID
WHERE OrderDate = '2005-08-01';

Here is one more example. I can access columns from other tables, not just INSERTED and DELETED:

 

UPDATE A 
SET CreatedBy = H.SalesPersonID 
OUTPUT inserted.SalesOrderID, H.AccountNumber 
FROM #SalesAudit A 
JOIN Sales.SalesOrderHeader H ON A.SalesOrderID = H.SalesOrderID ;

Keep OUTPUT in mind when you need to keep track of rows you have manipulated, whether it is to save to an audit table or just as a sanity check.

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.