Introduction

In this chapter, you will learn about temporary tables and how they are created, accessed, queried, indexed, and deleted. You will also learn the differences between temporary tables and views.

Definitions

As discussed earlier in this book, a view is a saved query that queries one or more tables. They are commonly used to restrict data from users for security purposes, shorten complex queries, and combine data from multiple tables. Views are very useful in Microsoft Access since they enable you to query data in the database in much the same way as you would a table.

Temporary tables are created for many of the same reasons you would create views. If you do not necessarily need to access up-to-date information stored in the database, temporary tables can be a great alternative to views.

In many DBMSs a temporary table is referred to as a table that exists temporarily in a database and is automatically dropped once you log out of the database. However, in Microsoft Access this is not the case since a temporary table in Access is not deleted unless you manually delete it.

In Microsoft Access a temporary table is a table that encompasses the result of a saved SELECT statement.

Even though temporary tables can be a nice alternative to views, keep in mind that there are some major differences between the two. First, a view is not a table and does not store data, whereas a temporary table is a table and actually contains data. Second, when you change data in a view, the data stored in the underlying tables is also changed. However, when you change data in a temporary table, it only affects the data stored in the temporary table and does not affect data stored in the actual tables. Let’s take another look at the view we created earlier in the book.

Creating a View

The following script creates a view that stores information from the Computers table in Figure 12-1. The view includes the following columns from the Computers table: SerialNum, Brand, and OfficeNumber.

Example 1

Figure 12-1. Computers table

The following script creates a view:

SELECT SerialNum, Brand, OfficeNumber
FROM Computers;

To save the view, follow these steps:

1. Click the Microsoft Office button and select Save from the drop-down menu.

Figure 12-2. Saving the view

2. When the Save As dialog box appears, type ComputerBrandLoc and click OK.

Figure 12-3. Naming the view

3. Next, click Queries from the View drop-down in Access 2007 to view your new view named ComputerBrandLoc.

Figure 12-4. Viewing the view

Note: Refer to Chapter 10 for alternative methods of creating views in different versions (SQL-89 and SQL-92) of Microsoft Access.

Creating a Temporary Table

Creating temporary tables in Access is slightly different from creating views in Access. Since temporary tables encompass the result of a saved SELECT statement, you must create or use a SELECT statement within your script. You also must create a name for your temporary table and use the INTO keyword within your script. Take a look at Example 2, which creates a temporary table.

Example 2

Create a temporary table that uses a SELECT statement to query the Computers table in Figure 12-1.

Note: After you type and execute the following script, click Yes to paste the rows into the new table called Temp1.

SELECT SerialNum, Brand, OfficeNumber INTO Temp1
FROM Computers;

This script displays three columns (SerialNum, Brand, OfficeNumber) from the Computers table. Typing INTO Temp1 after the columns specified in the SELECT statement causes Microsoft Access to create a temporary table called Temp1.

To view the temporary table, type the following script:

SELECT *
FROM Temp1;

Take a look at the results in Figure 12-5.

Figure 12-5. Results (output)

Accessing the Temporary Table

Temporary tables are located in the Tables group in the Navigation pane on the left side of the Microsoft Access 2007 window. To access the Temp1 table, choose Tables from the View drop-down and then double-click the Temp1 table in the list of tables.

Take a look at Figure 12-6.

Figure 12-6. Locating the Temp1 table

Querying a Temporary Table

Once you create a temporary table you can query it just like you would a normal table. The following example creates a query that queries the Temp1 table.

Example 3

Suppose you want to create a query that displays information on Dell computers in the Temp1 table. Look at the following script:

SELECT *
FROM Temp1
WHERE Brand = 'Dell'
ORDER BY SerialNum;

This script implements a simple SELECT statement that retrieves every column from the Temp1 table where the Brand is Dell. The ORDER BY clause sorts the output by the SerialNum column. Figure 12-7 shows the results from the query.

Figure 12-7. Results (output)

Indexing a Temporary Table

Temporary tables can be indexed just like you would an ordinary table. Remember, indexes help to retrieve records much faster because the DBMS must only search through presorted records rather than through every record in a table until a match is found. An index sorts and saves the values of a column in a different location on the computer with a pointer to the presorted records. Refer to Chapter 11 for more on indexes. Example 4 creates an index on the Temp1 table.

CREATE UNIQUE INDEX SerialNumIdx
ON Temp1 (SerialNum)
WITH DISALLOW NULL;

Updating a Temporary Table

As we stated earlier, a temporary table can be updated without affecting the data stored in the main tables of the database. When you update a view it automatically updates the tables where the data is actually stored. Take a look at Example 5.

Example 5

Figure 12-8. Temp1 table

Suppose you want to update the office number from 22 to 123 for serial number W2121040244 in the Temp1 temporary table in Figure 12-8. Look at the following script:

UPDATE Temp1
SET OfficeNumber = 123
WHERE OfficeNumber = 22
AND SerialNum = 'W2121040244';

This script implements an UPDATE statement that updates the Temp1 table. Figure 12-9 shows the updated office number in the Temp1 temporary table.

Figure 12-9. Temp1 table

Copying Records from One Temporary Table to a New Temporary Table Simultaneously

Example 6

Suppose you want to create a new temporary table and copy records from another temporary table into your new table at the same time. Look at the following script:

SELECT *
INTO Temp2
FROM Temp1;

This script creates a new temporary table named Temp2 and copies the records from the Temp1 table into the new Temp2 table. It uses the SELECT and FROM keywords to specify the table name (Temp1) and column names (* specifies all columns) from which to retrieve the records to insert. The INTO keyword is used to create a table named Temp2 and to insert the records retrieved from the table (Temp1) specified after the FROM keyword. Figure 12-10 shows the populated Temp2 table.

Figure 12-10. Temp2 table

Deleting a Temporary Table

Temporary tables are deleted just like regular tables in a database. Unlike temporary tables in most other DBMSs, they are not automatically dropped when you close down or log out of the database. Example 7 shows an example of how to delete a temporary table.

DROP TABLE Temp1;

Why Do We Need Temporary Tables?

After reading this chapter, you may still wonder “Why do I need temporary tables?” There are two major reasons: convenience and necessity. The first reason should be obvious. When you have a complex query that takes a long time to run and returns a small set of records as a result, you might want to keep this resulting set available, especially if you refer to it in several subsequent steps of your process. Remember, every time a query is run, you have to rebuild the result. If it takes 10 minutes to run a particular query, it means that each time that query is used, you spend another 10 minutes getting the answer. When you are only talking about a few records, it becomes worthwhile to spend the second necessary to save the query as a temporary table, then reference the temporary table.

The other time you need temporary tables is when you want to update records in a dataset that would not otherwise be updateable. An example of this is when you want to remove duplicate records from a table. Access is smart enough to know that the group by query produces a result set that is not unique. In other words, more than one record could have been used to produce a resulting record. So, since the records can refer to more than one record, the recordset is uneditable.

Examine the following records.

Figure 12-11

In this simple case, you have some names that are duplicated. The easy way to identify the duplicates is by creating a query (Figure 12-12) that groups the records by first and last name, determines which names have more than one record, and lists the ID of those records that are duplicates.

Figure 12-12

By joining this query to the original table (Figure 12-13), you can quickly select those records that are duplicates. There is a small problem, however; you cannot delete the records since the underlying query contains non-unique records.

Figure 12-13

A temporary table lets you get around the problem since Access does not care where it got its values from, just that it follows all the rules and therefore is updateable. So, to go back to our example, we turn the findDuplicates query into a make-table query (Figure 12-14) and have it build a temporary table that duplicates the results from the original query. We then can build our join and the records can then be deleted.

Figure 12-14

Summary

In this chapter, you learned the difference between a view and a temporary table. You also learned how to create, access, query, update, create an index for, and delete a temporary table.