Temporary Tables – Part 1

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 tiny AUTOS database I created in earlier article. The cool thing about this example is that the data is from in-line VALUES and not from a table.

I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.

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.

The SELECT 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.

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.

Derived tables can be used as the target of an UPDATE 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.

Derived tables can not be inserted into directly since they are temporary tables. However, they can be used as the source of an INSERT opertion. The query below inserts two new ASIA departments like their US counterparts.

Last but not least, derived tables can be used as the target of a DELETE statement.

Derived tables are useful when you want to combine the results of several inner SELECT sub-queries into one large result set. I like 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.

I do not like 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.

Related posts

2 Thoughts to “Temporary Tables – Part 1”

  1. Like the fresh appearance off the site. I enjoyed this article. Bless you for a excellent blog post.

  2. Simply a smiling visitant here to share the love (:, btw great design .

Leave a Comment