{"id":565,"date":"2011-07-22T14:49:06","date_gmt":"2011-07-22T14:49:06","guid":{"rendered":"http:\/\/craftydba.com\/?p=565"},"modified":"2011-07-22T15:17:08","modified_gmt":"2011-07-22T15:17:08","slug":"output-clause","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=565","title":{"rendered":"Output Clause"},"content":{"rendered":"

The OUTPUT<\/a> 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<\/a>, UPDATE<\/a>, or DELETE<\/a> statement. These results can be inserted into a table or variable to implement business requirements such as confirmation messages, archiving, and atomic transactions.<\/p>\n

Today, I would like to use the AdventureWorks<\/a> Sample database as the basis of our discussion.<\/p>\n

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.<\/p>\n

-- MS Sample database\r\nUSE [AdventureWorks2008R2]; GO\r\n\r\n-- New Table\r\nCREATE TABLE [Person].[CountryByContinent](\r\n\t[Country] [nvarchar](30) NOT NULL,\r\n\t[Continent] [nvarchar](30) NOT NULL,\r\n\t[ModifiedDate] [datetime] NOT NULL DEFAULT GETDATE()\r\n) ON [PRIMARY]; GO\r\n\r\n-- Add Primary Key\r\nALTER TABLE [Person].[CountryByContinent] ADD \r\nCONSTRAINT pkCountry PRIMARY KEY (Country); GO\r\n\r\n-- Add data to table, return as record set\r\nINSERT INTO [Person].[CountryByContinent] ([Country], [Continent]) \r\nOUTPUT INSERTED.*\r\nVALUES\r\n  ('Argentina', 'South America'),\r\n  ('Bolivia', 'South America'),\r\n  ('Brazil', 'South America'),\r\n  ('Chile', 'South America'),\r\n  ('Colombia', 'South America'),\r\n  ('Ecuador', 'South America'),\r\n  ('Guyana', 'South America'),\r\n  ('Paraguay', 'South America'),\r\n  ('Peru', 'South America'),\r\n  ('Suriname', 'South America'),\r\n  ('Uruguay', 'South America'),\r\n  ('Venezuela', 'South America'); GO\r\n<\/span><\/pre>\n
 <\/p>\n
An audit table is going to be created in the STAGE schema to show how to perform auditing when updating and deleting records.<\/p>\n
-- Save updates to audit table\r\nCREATE TABLE [Stage].[Audit](\r\n\t[Action] [nvarchar](30) NOT NULL,\r\n\t[Country] [nvarchar](30) NOT NULL,\r\n\t[Continent] [nvarchar](30) NOT NULL,\r\n\t[ModifiedDate] [datetime] NOT NULL DEFAULT GETDATE()\r\n) ON [PRIMARY]; GO\r\n<\/span><\/pre>\n
 <\/p>\n
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.<\/p>\n
-- Update a row and send before row to audit table\r\nUPDATE [Person].[CountryByContinent]\r\nSET [Country] = 'Chillis Grill & Bar'\r\n  OUTPUT 'UPDATE RECORD', DELETED.Country, DELETED.Continent\r\n  INTO [Stage].[Audit] ([Action], [Country], [Continent])\r\nWHERE [Country] = 'Chile'; GO  \r\n<\/span><\/pre>\n
 <\/p>\n
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.<\/p>\n
-- Delete a row and data to audit table\r\nDELETE FROM [Person].[CountryByContinent]\r\n  OUTPUT 'DELETE RECORD', DELETED.Country, DELETED.Continent\r\n  INTO [Stage].[Audit] ([Action], [Country], [Continent])\r\nWHERE [Country] = 'Chillis Grill & Bar'; GO  \r\n<\/span><\/pre>\n
 <\/p>\n
Last but not least, the OUTPUT clause can redirect row data to a table variable.  If STORED PROCEDURES<\/a> 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.<\/p>\n
-- Reload the staging table\r\nDELETE FROM STAGE.Person;\r\nINSERT INTO STAGE.Person SELECT * FROM Person.Person; GO\r\n\r\n-- Declare a table variable\r\nDECLARE @MyTable TABLE (\r\n\t[BusinessEntityID] [int],\r\n\t[PersonType] [nchar](2),\r\n\t[FirstName] [dbo].[Name],\r\n\t[LastName] [dbo].[Name] \r\n); \r\n\r\n-- Remove all Ken & John persons\r\nDELETE FROM STAGE.Person \r\nOUTPUT DELETED.BusinessEntityID, DELETED.PersonType, DELETED.FirstName, DELETED.LastName\r\nINTO @MyTable\r\nWHERE FirstName = 'Ken' OR FirstName = 'John';\r\n\r\n-- Impact of purging\r\nSELECT M.PersonType, M.FirstName, COUNT(*) AS Total\r\nFROM @MyTable M\r\nGROUP BY M.PersonType, M.FirstName\r\nORDER BY M.PersonType, M.FirstName; GO\r\n<\/span><\/pre>\n
 <\/p>\n
Overall, I think the OUTPUT statement allows for archiving of data without using AFTER TRIGGERS<\/a>.  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.<\/p>\n
I purposely left out mutliple TSQL statements can be Atomic as a group and pass the ACID<\/a> test with this new clause.  I will examine this concurrent SELECT\/UPDATE issue in a later talk.<\/p>\n
As usual, here is the output from the INSERT, DELETE, and AUDIT examples.<\/p>\n
output-clause-insert<\/a>
\noutput-clause-delete<\/a>
\noutput-audit-entries<\/a><\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,12,15,34,28,29],"class_list":["post-565","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-free-code","tag-john-f-miner-iii","tag-output-clause","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/565","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=565"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/565\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}