The OUTPUT clause was introduce with the release of SQL Server 2005. This clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be inserted into a table or variable to implement business requirements such as confirmation messages, archiving, and atomic transactions.
Today, I would like to use the AdventureWorks Sample database as the basis of our discussion.
The first example creates a new Country By Continent table to store all the countries in South America. The OUTPUT clause is used with the INSERT statement to add data to the table and return the result set to the calling application, SQL Server Management Studio (SSMS). This example demostrates how a confirmation message could be coded in TSQL by the application.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<span style="color: #008000;">-- MS Sample database USE [AdventureWorks2008R2]; GO -- New Table CREATE TABLE [Person].[CountryByContinent]( [Country] [nvarchar](30) NOT NULL, [Continent] [nvarchar](30) NOT NULL, [ModifiedDate] [datetime] NOT NULL DEFAULT GETDATE() ) ON [PRIMARY]; GO -- Add Primary Key ALTER TABLE [Person].[CountryByContinent] ADD CONSTRAINT pkCountry PRIMARY KEY (Country); GO -- Add data to table, return as record set INSERT INTO [Person].[CountryByContinent] ([Country], [Continent]) OUTPUT INSERTED.* VALUES ('Argentina', 'South America'), ('Bolivia', 'South America'), ('Brazil', 'South America'), ('Chile', 'South America'), ('Colombia', 'South America'), ('Ecuador', 'South America'), ('Guyana', 'South America'), ('Paraguay', 'South America'), ('Peru', 'South America'), ('Suriname', 'South America'), ('Uruguay', 'South America'), ('Venezuela', 'South America'); GO </span> |
An audit table is going to be created in the STAGE schema to show how to perform auditing when updating and deleting records.
|
1 2 3 4 5 6 7 8 |
<span style="color: #008000;">-- Save updates to audit table CREATE TABLE [Stage].[Audit]( [Action] [nvarchar](30) NOT NULL, [Country] [nvarchar](30) NOT NULL, [Continent] [nvarchar](30) NOT NULL, [ModifiedDate] [datetime] NOT NULL DEFAULT GETDATE() ) ON [PRIMARY]; GO </span> |
As a system administrator, I get hungry around dinner time and by mistake I updated the country CHILE to represent the resturant that has great ribs. The output of the UPDATE statement is redirected to an AUDIT table.
|
1 2 3 4 5 6 7 |
<span style="color: #008000;">-- Update a row and send before row to audit table UPDATE [Person].[CountryByContinent] SET [Country] = 'Chillis Grill & Bar' OUTPUT 'UPDATE RECORD', DELETED.Country, DELETED.Continent INTO [Stage].[Audit] ([Action], [Country], [Continent]) WHERE [Country] = 'Chile'; GO </span> |
After having my dinner, I discover my mistake and decide to delete the record. Again, the output of the DELETE statement is redirected to the the AUDIT table.
|
1 2 3 4 5 6 |
<span style="color: #008000;">-- Delete a row and data to audit table DELETE FROM [Person].[CountryByContinent] OUTPUT 'DELETE RECORD', DELETED.Country, DELETED.Continent INTO [Stage].[Audit] ([Action], [Country], [Continent]) WHERE [Country] = 'Chillis Grill & Bar'; GO </span> |
Last but not least, the OUTPUT clause can redirect row data to a table variable. If STORED PROCEDURES are used to create a data access layer for the front end application, this opens up a whole new area of possibilites in which aggregation of the modified data can be returned or recorded. The example below removes all persons who’s name start with KEN or JOHN and saves key data into a table variable. This deleted data is summarized to find out the break down by person type and name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<span style="color: #008000;">-- Reload the staging table DELETE FROM STAGE.Person; INSERT INTO STAGE.Person SELECT * FROM Person.Person; GO -- Declare a table variable DECLARE @MyTable TABLE ( [BusinessEntityID] [int], [PersonType] [nchar](2), [FirstName] [dbo].[Name], [LastName] [dbo].[Name] ); -- Remove all Ken & John persons DELETE FROM STAGE.Person OUTPUT DELETED.BusinessEntityID, DELETED.PersonType, DELETED.FirstName, DELETED.LastName INTO @MyTable WHERE FirstName = 'Ken' OR FirstName = 'John'; -- Impact of purging SELECT M.PersonType, M.FirstName, COUNT(*) AS Total FROM @MyTable M GROUP BY M.PersonType, M.FirstName ORDER BY M.PersonType, M.FirstName; GO </span> |
Overall, I think the OUTPUT statement allows for archiving of data without using AFTER TRIGGERS. This is ideal when a data access layer developed with STORED PROCEDURES is used. I can also see how returning record sets allow the front end to optionally implement confirmation messages.
I purposely left out mutliple TSQL statements can be Atomic as a group and pass the ACID test with this new clause. I will examine this concurrent SELECT/UPDATE issue in a later talk.
As usual, here is the output from the INSERT, DELETE, and AUDIT examples.
output-clause-insert
output-clause-delete
output-audit-entries
I hope you have been enjoying my blog. If there is something that you really want explained with an example, please send me a email.
This is very helpful, thanks for revealing. I will be confident others will view items this similar style
Awesome post! I will keep an on eye on your blog.
Way cool, some valid points! I appreciate you creating this post obtainable, the rest of the internet site is also top quality. Have a fun.
You have remarked very interesting points! ps decent internet site.
Just wanna comment on few general things, The website style is perfect, the subject matter is really excellent : D.
Wow! Thank you! I continuously needed to write on my website something like that. Can I include a part of your post to my website?
Keep to focus your point, this is nice blog.
I have been reading out some of your posts and it’s nice stuff. I will definitely bookmark your website.
Great post, I conceive blog owners should acquire a lot from this site its really user pleasant. So much good information on here :D.
Bookmarked this post and look forward to coming back again. Excellent work!
yeah bookmaking this wasn’t a bad determination outstanding post! .