|
|
Temporary tables are tables that exist for a limited period of time or are limited to the current session or transaction. They're particularly useful in triggers or stored procedures when you need to quickly create a table for various tasks. For example, you can use temporary tables when you need to compare a set of data with the current data in the database. If you're thinking of using a cursor but could use a temporary table instead, then it's generally a good idea to use a temporary table (because cursors are bad for performance).
Let's quickly look at a simple example. You'll create a temporary table and fill it with the names of all the students and the average mark they scored over all their exams. You'll then query this table to find the students who did particularly well or particularly badly.
In SQL Server, you create temporary tables just like normal tables except that they have names beginning with # or ##. You use # for local temporary tables (which are visible only to the connection that created them) or ## for global temporary tables (which are visible to all connections).
SQL Server temporary tables are always created in the tempdb system database. If a temporary table isn't dropped explicitly by the connection that created it, it's usually dropped automatically as soon as the connection is closed.
 |
Enter and execute the following lines in Query Analyzer:
CREATE TABLE #tmp (StudentName VARCHAR(50), AverageMark INT);
INSERT INTO #tmp
SELECT Student.Name AS StudentName,
AVG(Mark) AS AverageMark
FROM StudentExam
INNER JOIN Student
ON StudentExam.StudentID = Student.StudentID
GROUP BY StudentName;
SELECT StudentName, AverageMark FROM #tmp
WHERE AverageMark < 50;
SELECT StudentName, AverageMark FROM #tmp
WHERE AverageMark > 70;
Here you create a temporary table called #tmp and populate it using the INSERT INTO syntax you saw in Chapter 3, "Modifying Data." You then run two queries against this table, exactly as you would against a normal table:
StudentName AverageMark --------------- ------------ Gary Burton 48 StudentName AverageMark --------------- ------------ Emily Scarlett 80 Maria Fernandez 73 Steve Alaska 71
|
In Oracle, you can create a temporary table using the CREATE GLOBAL TEMPORARY TABLE command instead of the usual CREATE TABLE. When you create a temporary table, you can specify whether the data should be preserved for the current session (by specifying an ON COMMIT PRESERVE ROWS clause) or only for the current transaction (ON COMMIT DELETE ROWS). Oracle doesn't automatically allocate space for temporary tables as it does for normal tables: Space will be dynamically allocated as new rows are inserted.
Although the data in Oracle temporary tables is transitory, the tables themselves aren't—after you've created a temporary table, it will remain available until you drop it (using the normal DROP TABLE command).
|
Execute the following query in SQL*Plus:
CREATE GLOBAL TEMPORARY TABLE tmp
AS
SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
FROM StudentExam
INNER JOIN Student
ON StudentExam.StudentID = Student.StudentID
GROUP BY Student.Name;
INSERT INTO tmp
SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
FROM StudentExam
INNER JOIN Student
ON StudentExam.StudentID = Student.StudentID
GROUP BY Student.Name;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark < 50;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark > 70;
Again, you create a temporary table called tmp to store the names and average marks of the students. Notice that you don't provide a list of column names and data types to define the table, but you provide a SELECT statement. This statement doesn't, however, fill the table, so you do that using a SELECT INTO statement. You can then query the table to find the students with very good or very poor averages:
StudentName AverageMark --------------- ------------ Gary Burton 48 StudentName AverageMark --------------- ------------ Emily Scarlett 80 Maria Fernandez 73 Steve Alaska 71
|
DB2 uses the DECLARE GLOBAL TEMPORARY TABLE statement to create a temporary table. You can define the table using either a standard list of columns, as in SQL Server, or a SELECT statement, as in Oracle.
When you create a temporary table in DB2, you need to tell DB2 where to put the table; to do this, you have to create a tablespace. There are different types of tablespaces in DB2, but to store temporary tables, you need to create a USER TEMPORARY tablespace. This type of tablespace can be accessed by users of the database (as opposed to system tablespaces, which can only be accessed by DB2 itself) and will be destroyed once the connection is closed. Tablespaces can be managed either by the system or by the database itself.
|
Execute the following query in Command Center:
CREATE USER TEMPORARY TABLESPACE IUTemp
MANAGED BY SYSTEM USING ('C:\IUTemp_tbsp');
DECLARE GLOBAL TEMPORARY TABLE tmp (
StudentName VARCHAR(50), AverageMark INT) IN IUTemp;
INSERT INTO SESSION.tmp
SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
FROM StudentExam
INNER JOIN Student
ON StudentExam.StudentID = Student.StudentID
GROUP BY Student.Name;
SELECT StudentName, AverageMark FROM SESSION.tmp WHERE AverageMark < 50;
SELECT StudentName, AverageMark FROM SESSION.tmp WHERE AverageMark > 70;
Here you create a system-managed USER TEMPORARY tablespace called IUTemp, specifying that the data files will be stored in the C:\IUTemp_tbsp directory (change this as necessary for your system). This directory doesn't have to exist in advance—DB2 will create it if necessary:
CREATE USER TEMPORARY TABLESPACE IUTemp
MANAGED BY SYSTEM USING ('C:\IUTemp_tbsp');
Once you've created the tablespace, you can create the table itself using the DECLARE GLOBAL TEMPORARY TABLE command. In this case, you specify the column definitions for the table explicitly, rather than using the AS SELECT syntax you saw with Oracle. Notice the IN clause, which tells DB2 which tablespace to create the table in:
DECLARE GLOBAL TEMPORARY TABLE tmp ( StudentName VARCHAR(50), AverageMark INT) IN IUTemp;
Once you've created the table, you can populate it with an INSERT INTO...SELECT statement. Notice that the table is by default created in the SESSION schema, not the current user's schema, so you prefix the table name with SESSION (SESSION.tmp). You can then query this table as normal:
StudentName AverageMark --------------- ------------ Gary Burton 48 StudentName AverageMark --------------- ------------ Emily Scarlett 80 Maria Fernandez 73 Steve Alaska 71
|
MySQL has the simplest syntax—you create temporary tables using the CREATE TEMPORARY TABLE statement, which has the same syntax as a normal CREATE TABLE statement. Temporary tables can be particularly useful in MySQL as a way of getting around the lack of support for subqueries.
|
Execute the following query at the mysql command prompt:
CREATE TEMPORARY TABLE tmp (StudentName VARCHAR(50), AverageMark INT);
INSERT INTO tmp
SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
FROM StudentExam
INNER JOIN Student
ON StudentExam.StudentID = Student.StudentID
GROUP BY Student.Name;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark < 50;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark > 70;
In this example, you just create a temporary table named tmp with the StudentName and AverageMark columns and populate it using the INSERT INTO...SELECT statement. You then run your two queries against the tmp table:
StudentName AverageMark --------------- ------------ Gary Burton 48 StudentName AverageMark --------------- ------------ Emily Scarlett 80 Maria Fernandez 73 Steve Alaska 71
|
|
|