{"id":124,"date":"2008-12-31T14:35:38","date_gmt":"2008-12-31T12:35:38","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=124"},"modified":"2013-04-06T19:24:32","modified_gmt":"2013-04-06T17:24:32","slug":"on-the-output-of-a-data-modification","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/12\/31\/on-the-output-of-a-data-modification\/","title":{"rendered":"On the OUTPUT of a data modification"},"content":{"rendered":"

or “Who needs a trigger anyway?<\/em>”<\/p>\n

The output clause was, I think, one of those wonderful features of SQL 2005 that very few people used, myself included. Now in 2008, it’s even better, but still doesn’t appear to be widely used.<\/p>\n

The output clause can be used to get, as a resultset, data from the inserted and deleted tables that are usually only visible in a trigger. As a very simple example:<\/p>\n

Create Table #Testing (\r\nid int identity,\r\nsomedate datetime default getdate()\r\n)\r\n\r\ninsert into #Testing\r\noutput inserted.*\r\ndefault values <\/pre>\n
Neat. We can get back the inserted values as a result set. We can also insert them into a table variable for later processing. Using the same temp table<\/p>\n
declare @OutputTable TABLE (id int, somedate datetime)\r\n\r\ninsert into #Testing\r\noutput inserted.* into @OutputTable\r\ndefault values\r\n\r\nselect * from @OutputTable <\/pre>\n
Very neat. Now how about a practical example? Say we have the following three tables in a database.<\/p>\n
Create Table ParentTable (\r\nID int identity primary key,\r\nParentDescription varchar(50),\r\nCreationDate DATETIME DEFAULT GETDATE()\r\n)\r\nGO\r\nCREATE TABLE ChildTable (\r\nID Int identity Primary Key,\r\nParentID int not null constraint fk_parent foreign key references ParentTable(ID),\r\nSomedescription varchar(20),\r\nSomeValue Money\r\n)\r\nGO\r\n\r\nCreate Table AuditTable (\r\nAuditID int identity primary key,\r\nChildID int,\r\nSomeValue Money,\r\nInsertDate DATETIME DEFAULT GETDATE(),\r\nOriginatingLogin VARCHAR(50) DEFAULT ORIGINAL_LOGIN()\r\n)<\/pre>\n
We get a set of data (perhaps in a temp table, perhaps in an xml document) that needs to be inserted into those tables. The source data will have multiple parent rows, each with multiple child rows. Those need to be inserted into the appropriate tables and the foreign keys have to be assigned correctly. In addition, the ID of the child rows, along with the value and the current date must be written into an audit table, along with the login name of the current user.<\/p>\n
It’s not a difficult requirement, but because the IDs are assigned when the insert happens, it requires the data be selected back, or the use of a trigger. @@identity (or the other identity functions) can’t be used because there will be multiple rows.<\/p>\n
So, how will the output clause help us here? First, for SQL 2005.<\/p>\n
Source data:<\/p>\n
CREATE TABLE #SourceData (\r\nParentDescription Varchar(50),\r\nChildDescription varchar(50),\r\nTheValue Money\r\n)\r\n\r\ninsert into #SourceData (ParentDescription, ChildDescription, TheValue)\r\nvalues ('aaa', 'a1', 1.02)\r\ninsert into #SourceData (ParentDescription, ChildDescription, TheValue)\r\nvalues ('aaa', 'a2', 58.2)\r\ninsert into #SourceData (ParentDescription, ChildDescription, TheValue)\r\nvalues ('aaa', 'b1', 18.42)\r\ninsert into #SourceData (ParentDescription, ChildDescription, TheValue)\r\nvalues ('bbb', 'c1', 0.59)\r\ninsert into #SourceData (ParentDescription, ChildDescription, TheValue)\r\nvalues ('ccc', 'z4', 78.25)\r\ninsert into #SourceData (ParentDescription, ChildDescription, TheValue)\r\nvalues ('ccc', 'z5', 85.2) <\/pre>\n
Insert code:<\/p>\n
DECLARE @Parent Table (id int, Descr Varchar(50))\r\nDECLARE @Child TABLE (id int, parentID int, SomeValue money)\r\n\r\nINSERT INTO ParentTable (ParentDescription)\r\nOutput Inserted.ID, Inserted. ParentDescription Into @Parent\r\nSELECT DISTINCT ParentDescription from #SourceData\r\n\r\nINSERT INTO ChildTable (ParentID, Somedescription, someValue)\r\nOutput inserted.ID, Inserted.ParentID, Inserted.someValue into @Child\r\nSELECT ID, ChildDescription, TheValue\r\nFROM @Parent p inner join #SourceData s on p.Descr = s.ParentDescription\r\n\r\nINSERT INTO AuditTable (StartParentRange, EndParentRange, StartChildRange, EndChildRange, TotalChildRows, TotalValue)\r\nSELECT Min(ParentID), MAX(ParentID), Min(id), max(id), Count(*), SUM(SomeValue)\r\nFROM @Child<\/pre>\n
That’s fairly nice. No triggers, no need to insert the parents 1 by 1 to get at the identity values, no need to reselect from the tables after the insert (which can be expensive if they’re large)<\/p>\n
On SQL 2005, that’s the best that’s possible, as the output can only be inserted into a table variable, or returned as a result set.<\/p>\n
In 2008, the output from a data modification can be used as the source for another insert statement. There are a lot of restrictions to it, so it’s not the most useful of features at the moment. Joins are not allowed so the data modification has to be the sole source for the second insert statement. Aggregations are also not allowed. The destination table must not have foreign keys. Still it will allow us to reduce the table variables by one.<\/p>\n
With the same source data as the prior example, for SQL 2008:<\/p>\n
DECLARE @Parent Table (id int, Descr Varchar(50))\r\n\r\nINSERT INTO ParentTable (ParentDescription)\r\nOutput Inserted.ID, Inserted. ParentDescription Into @Parent\r\nSELECT DISTINCT ParentDescription from #SourceData\r\n\r\nInsert into AuditTable (ChildID, SomeValue)\r\nSELECT id, SomeValue\r\nFROM\r\n(INSERT INTO ChildTable (ParentID, Somedescription, someValue)\r\nOutput inserted.ID, Inserted.someValue\r\nSELECT ID, ChildDescription, TheValue\r\nFROM @Parent p inner join #SourceData s on p.Descr = s.ParentDescription) AS i (id, somevalue)<\/pre>\n
With all the restrictions on the nested inserts, they’re not as useful as they seem, but it is an interesting technique, has at least a few uses and hopefully will be less restricted in future versions.<\/p>\n","protected":false},"excerpt":{"rendered":"
or “Who needs a trigger anyway?” The output clause was, I think, one of those wonderful features of SQL 2005 that very few people used, myself included. Now in 2008, it’s even better, but still doesn’t appear to be widely… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,17],"tags":[],"class_list":["post-124","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-20","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/124","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=124"}],"version-history":[{"count":3,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/124\/revisions"}],"predecessor-version":[{"id":1428,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/124\/revisions\/1428"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=124"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=124"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=124"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}