In this lesson, you will learn about temp tables.

We've lightly touched on temp tables earlier in the tutorials, but now we're going to see just how powerful they are. As a quick review, the following scenarios are the best place to use temporary tables:

  • Data needs to be updated in multiple steps.
  • Data needs to exist for more than a single query.
  • One or more processes might need to access the data.
  • Data doesn't need to exist forever.
  • Readability is more important than speed.

As we'll see in today's lesson, there are some things that just cannot be accomplished in a single SQL statement. When you encounter one of these situations, a temp table serves as a staging area to rearrange data or create relationships.

Creating A Temp Table

Creating a temporary table is exactly like creating a normal, permanent table. The only difference is that we put a # sign in front of the name. You can use a CREATE or SELECT INTO to initiate the table the same way we've learned with in previous lessons.

This statement will create an exact duplicate of the permanent table called classes:

SELECT * 
INTO #OldClasses
FROM Classes

This one creates a table from scratch and adds two rows to it:

CREATE TABLE #NewCodes (Code varchar(50), TestID int)

INSERT INTO #NewCodes (Code, TestID) VALUES ('A', 1)
INSERT INTO #NewCodes (Code, TestID) VALUES ('B', 2)

This statement removes the table we just made:

DROP TABLE #NewCodes

How Long Does a Temp Table Exist

You might be asking yourself, what exactly is the difference between temporary tables and normal ones. The temporary table only exists while the current SQL connection is open. It's also only available via that connection under normal circumstances.

To illustrate, open a new SQL query window and run the following code:

CREATE TABLE #NewCodes (Code varchar(50), TestID int)

INSERT INTO #NewCodes (Code, TestID) VALUES ('A', 1)
INSERT INTO #NewCodes (Code, TestID) VALUES ('B', 2)

SELECT * FROM #NewCodes

You'll see the results appear as expected. However, when you try to run it again, you'll get an error saying, “Table already exists.” Now, open a new query window. Try to run just the SELECT statement. The table cannot be found. However, if you run this statement in the original window by itself, you'll get your data back again.

Now close all the windows and open a new one. If you attempt to run the SELECT in the new window, you'll get an error. The #NewCodes table was automatically destroyed when you closed the query window that created it. So, why do we use a temp table? Let's look at a complicated update situation that is just too difficult to pull off with a single statement.

The Multi-Table Update

Now, let's say we have a database for a chain of sports stores. Here's our database schema:

temp table

As you can see, we have stores and products tied together by an intersecting table. Here's the data in each of the tables:

temp table

Now, the manager has uploaded a spreadsheet to update the intersecting table. It's called ProductMatch and contains the following:

temp table

The table should add any new products that do not already exist. Additionally, it must not add any duplicate rows to the StoreProduct table. Is this an easy update to make? It should be. But the problem is that we do not know the foreign key IDs to make the update on. We also have no way of quickly knowing if there are any rows that are already in StoreProduct.

We've also been asked not to modify the ProductMatch table since it will be used in other parts of the system. Are you feeling overwhelmed yet? Professional SQL development often consists of problems like this! The good news is, by breaking the puzzle down into smaller pieces and using temp tables, we can keep our sanity and solve the problems.

Let's start by making a temporary table where we can stage our data. In order to make ProductMatch into a table that has all the information needed to make the proper updates, we're going to need to correlate the Foreign Keys from the other tables to the string values on ProductMatch. Additionally, we'll want to have some way of filtering out rows that already are in StoreProduct. So we need three pieces of information for each row in ProductMatch:

  • The Store's primary key.
  • The Product's primary key.
  • Some way to know if it's already in StoreProduct.

So, let's make our temp table. It can be a temporary table since we won't need it after the update is made. If we make this into a reusable process, we won't have to worry about removing the data later on or anything like that. It'll be a smooth, repeatable process.

CREATE #tempMatch (Store varchar(50), Product varchar(50), StoreID int, ProductID int, inStoreProduct int)

Now we have an empty table that meets our needs. Let's review what we're going to do with each field.

  • Store – This will hold the text value from ProductMatch for store.
  • Product – This will hold the text value from ProductMatch for product.
  • StoreID – This will hold the primary key from Store that corresponds to the text value.
  • ProductID – This will hold the primary key from Product that corresponds to the text value.
  • StoreProductID –We'll put the StoreProductID in this column if the ProductMatch row already exists in store product.

Now, we're ready to start filling our temporary table. We'll get it ready piece by piece and in the end we'll have all the information needed to make the final update.

Step 1: Fill in the text values from ProductMatch

Essentially, this table is an extension of what is already in ProductMatch. So we'll start by filling it with the contents of ProductMatch. This is easy with an INSERT and SELECT combination.

INSERT INTO #tempMatch (Store, Product) 
SELECT Store, Product
FROM ProductMatch

SELECT * FROM #tempMatch
temp table

Notice that we didn't fill in all the columns that exist in #tempMatch. We don't have enough info to do that right now. And when we divide up our steps, it's much easier to keep everything straight in our heads and in our code.

Step 2: Getting the Primary Keys

Next, we need to figure out which primary keys go with which text value. When the time comes to insert, the word “Baseball” won't be sufficient in order to insert data. We'll need to know the primary key for Baseball in order to add it to StoreProduct.

UPDATE #tempMatch
SET StoreID = Store.StoreID
FROM #tempMatch 
INNER JOIN Store
ON #tempMatch.Store = Store.Store

UPDATE #tempMatch
SET ProductID = Product.ProductID
FROM #tempMatch
INNER JOIN Product
ON #tempMatch.Product = Product.ProductName

Notice that we joined on the text value. Normally, we'd join on a foreign and primary key. The problem is we do not know what that is right now, so the text value will have to get the job done. After running this statement, our table looks like this:

SELECT * FROM #tempMatch
temp table

Step 3: Checking For Duplicates

Now, we've almost got all the information we need to add the new records. The only thing we're missing weeding out any duplicates. We now have enough information to check if these rows already exist in StoreProduct.

UPDATE #tempMatch
SET StoreProductID = StoreProduct.StoreProductID
FROM #tempMatch
INNER JOIN StoreProduct
ON StoreProduct.StoreID = #tempMatch.StoreID
AND StoreProduct.ProductID = #tempMatch.ProductID

SELECT * FROM #tempMatch
temp table

It's a good thing we checked this for duplicates. We would have entered four duplicate entries into StoreProduct if we had simply assumed there was no duplicate data. Now we can filter out the duplicates and get everything loaded up correctly into StoreProduct.

Step 4: Inserting Data From Temp Table

INSERT INTO StoreProduct (StoreID, ProductID)
SELECT StoreID, ProductID
FROM #tempMatch
WHERE StoreProductID = 0 or StoreProductID IS NULL
8 row(s) inserted.

That's it! We just updated the StoreProduct table based on the ProductMatch table and our temporary table. You can see the critical role that the temp table plays in this process. We'll use this same example going forward as we learn about views, a much more permanent yet less physical version of the temp table.

Categories : SQL
Tags : SQL Basic


author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories