{"id":1144,"date":"2011-11-01T20:05:51","date_gmt":"2011-11-01T20:05:51","guid":{"rendered":"http:\/\/craftydba.com\/?p=1144"},"modified":"2011-11-01T20:36:11","modified_gmt":"2011-11-01T20:36:11","slug":"temporary-tables-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1144","title":{"rendered":"Temporary Tables – Part 1"},"content":{"rendered":"

Today, I am going to be starting a series of articles on temporary tables. I will be focusing on derived tables<\/a>. A derived table is a sub-query that is used to as input to the outer query. An table alias should be used outside the parenthesis to reference the inner query. I think of derived tables as a cross between temporary tables and dynamic views that go away after the query executes. <\/p>\n

The first example of a derived table creates the car ownership table for person a. Please see the tiny AUTOS database I created in earlier article<\/a>. The cool thing about this example is that the data is from in-line VALUES and not from a table.<\/p>\n

-- Database selection does not matter\r\nUSE model;\r\nGO\r\n\r\n-- Create a derived table\r\nSELECT * FROM \r\n(\r\n  VALUES \r\n  (1, 'Continental'),\r\n  (2, 'Edsel'),\r\n  (3, 'Lincoln'),\r\n  (4, 'Mercury'),\r\n  (5, 'Ram')\r\n) AS A (MyId, MyValue);\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
I will be using the Adventure Works <\/a>Sample database supplied by Microsoft during the rest of this talk.<\/p>\n
A common query pattern is to get the most recent id by date.  Joining these results to the original table will bring back the most recent record by date.<\/p>\n
The SELECT <\/a>query below uses the last sales order by customer id as the derived table (inner query).  The outer query returns customer name, sales id, sub-total and order date.  This might be a query for the SALES team to cold call customers that have not ordered in the past N months.<\/p>\n
-- Use the correct database\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n\r\n-- Get customer name, sales id, sub-total and order date\r\nSELECT \r\n  P.FirstName, \r\n  P.LastName,\r\n  SH.SalesOrderID, \r\n  SH.SubTotal,\r\n  SH.OrderDate\r\nFROM\r\n  Sales.SalesOrderHeader AS SH \r\n -- Derived table - last order by customer in 2008\r\n  INNER JOIN\r\n  (\r\n    SELECT \r\n      CustomerID AS MyCustomer,\r\n      MAX(OrderDate) AS MyDate\r\n    FROM \r\n      Sales.SalesOrderHeader\r\n    WHERE\r\n      YEAR(OrderDate) = 2008 \r\n    GROUP BY \r\n  \t  CustomerID\r\n  ) AS MC \r\n  ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate\r\n  INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID\r\n  INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID\r\nORDER BY \r\n  SH.OrderDate ASC;\r\nGO  \r\n<\/span><\/pre>\n<\/p>\n
I am going to create a duplicate of the department table from the Adventure Works database.  This table will be used in the examples below.<\/p>\n
-- Remove department staging table\r\nIF  EXISTS (\r\n    SELECT * \r\n    FROM sys.objects \r\n    WHERE object_id = OBJECT_ID(N'[STAGE].[Department]') AND type in (N'U')\r\n    )\r\n    DROP TABLE [STAGE].[Department]\r\nGO\r\n\r\n-- Recreate table from AW entries\r\nSELECT * INTO [STAGE].[Department] FROM [HumanResources].[Department]\r\nGO\r\n\r\n-- Add a default date\r\nALTER TABLE [STAGE].[Department] ADD CONSTRAINT df_Modified_Date\r\n  DEFAULT GETDATE() FOR [ModifiedDate];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Derived tables can be used as the target of an UPDATE <\/a>statement.  The query below updates the two Quality Assurance deparment names with a location ‘US’ as a prefix.  We are doing this to get ready for making a set of ASIA departments with the same job functions.<\/p>\n
-- Update group to US\r\nUPDATE T1\r\nSET GroupName = 'US Quality Assurance'\r\nFROM \r\n  (\r\n      SELECT * FROM [STAGE].[Department] \r\n      WHERE GroupName = 'Quality Assurance'\r\n  ) AS T1;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Derived tables can not be inserted into directly since they are temporary tables.  However, they can be used as the source of an INSERT <\/a>opertion.  The query below inserts two new ASIA departments like their US counterparts.<\/p>\n
-- Insert group for ASIA\r\nINSERT INTO [STAGE].[Department] (Name, GroupName)\r\nSELECT T1.Name, 'ASIA ' + SUBSTRING(T1.GroupName, 4, LEN(T1.GroupName) - 3) AS GroupName\r\nFROM\r\n    (\r\n      SELECT * FROM [STAGE].[Department] \r\n      WHERE GroupName = 'US Quality Assurance'\r\n    ) AS T1;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Last but not least, derived tables can be used as the target of a DELETE <\/a>statement.  <\/p>\n
-- Delete group for US    \r\nDELETE\r\nFROM T1\r\nFROM \r\n    (\r\n      SELECT DepartmentID FROM [STAGE].[Department] \r\n      WHERE GroupName = 'US Quality Assurance'\r\n    ) AS T1;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Derived tables<\/a> are useful<\/span> when you want to combine the results of several inner SELECT sub-queries into one large result set.  I like<\/span> the syntax for UPDATING and DELETING data using derived tables.  One can check the results of the inner query before executing the outer one.  Like any good carpenter (crafty dba), check twice and cut once. <\/p>\n
I do not like<\/span> the syntax for a derived table INSERT which increases the amount of typing without adding value.  Next time, I will talk about local and global temporary tables<\/span>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I am going to be starting a series of articles on temporary tables. I will be focusing on derived tables. A derived table is a sub-query that is used to as input to the outer query. An table alias should be used outside the parenthesis to reference the inner query. I think of derived tables as a cross between temporary tables and dynamic views that go away after the query executes. The first example of a derived table creates the car ownership table for person a. Please see the…<\/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":[33,182,12,30,15,37,28,29,32],"class_list":["post-1144","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-delete","tag-derived-tables","tag-free-code","tag-insert","tag-john-f-miner-iii","tag-select","tag-sql-server","tag-tsql","tag-update"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1144","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=1144"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1144\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}