Data processing may involve several steps. And it can be fun! But the fun ends when you access some joined tables again and again. So, when you look at your SQL code, it’s yucky and ugly because of repeated lines. But what if you can store the results of those joined tables in temporary storage? You can do it with a temp table, and SELECT INTO can help you with that.
But is it downright easy to code and manage?
It is, if you use a tip or two from this article. It will guide you in understanding what temporary tables are. And you will learn the syntax for one of the ways to create them. And without putting your database at a snail’s pace.
This article is benefit-driven. So, here are the points you will learn:
- Understanding the Power of Temporary Tables
- How to Create Temporary Tables with SELECT INTO
- How to Drop Temporary Tables Made with SELECT INTO
- How to Avoid Slowing Down Your Database When Using SELECT INTO Temp Table
Now, get your favorite drink, and let’s begin.
Understanding the Power of Temporary Tables
Simply put, temporary tables are storages of temporary data. It’s like a hotel. You visit an island beach resort and stay there during your most-awaited vacation. It’s your temporary shelter until it’s time to go home.
So, when do you need temporary tables?
When to Use Temporary Tables?
Sometimes, your report requirements require you to have several data processing steps. And during data retrieval, you need to store intermediate results. You may join these results to other tables. So, this will prompt you to use temporary tables.
Using temp tables can make your SQL code simpler.
Imagine a SELECT statement with several joins and derived tables. If the single query is too heavy, like lifting a sumo wrestler, your server can barely process that query. And your results may arrive too late for the next CEO meeting. That will be terrible!
To fix that, you can try to divide and conquer. Divide the process task into smaller ones. Separating some joined and derived tables are examples of these. You can use temporary tables for these purposes. Then, join them to the final query that will give the results suited for the report. Now, looking at your code, it becomes much more straightforward. But will it run fast? You can examine each query to find out. And if the query runs fast and smoothly, then that’s it.
At the outset of this article, I mentioned another use case. Repeated access to the same joined tables in a query may merit using temporary tables. Of course, you still need to examine the performance impact.
5 Things to Remember About Temporary Tables
There are things to remember when using temporary tables in SQL Server.
- They are session-specific. Your connection to SQL Server is your session. Once you cut the connection, the session is over. The temporary table’s lifetime is up to the time your session ends (or until you manually erase it). It is no longer accessible the next time you connect to SQL Server.
- You can set it as local or global. Local temporary tables are accessible only to the one who created them. And you can identify local temp tables with a single pound or hash sign (#) before its name. In contrast, global temporary tables are accessible to all active SQL Server sessions. It is also accessible until all sessions referencing it have ended. And you can identify them with a double hash sign (##).
- Column names should be unique. At times, you derive the columns of temporary tables from expressions. With this, you assign a unique alias when using SELECT INTO. You will trigger an error if there’s no column name or if the name is not unique.
- You can do to a temporary table the same things you do with regular tables. You can SELECT, INSERT, UPDATE, DELETE, and TRUNCATE them. And you can also use INDEX and add constraints. And finally, you can use DROP to erase them.
- Temporary tables live and die in TempDB. Knowing this allows you to prepare your TempDB so it won’t slow your queries down. More on this later.
The 2 Ways to Make Temporary Tables
You can either use CREATE TABLE or SELECT INTO. In this article, we will focus only on using SELECT INTO.
There are differences in using these 2 methods. Check out the table below.
| CREATE TABLE #temptable | SELECT INTO #temptable | |
| Structure | Built during CREATE TABLE | Built on SELECT |
| Data Availability | Created without data | Created with data |
| Indexes and Constraints | You can add them before inserting data | You can add them once data is available |
| Computed Columns | Set during CREATE TABLE | A computed column from the source becomes a regular column in the temporary table |
Knowing these differences will allow you to use the correct method for a given scenario.
How to Create Temporary Tables with SELECT INTO (With Examples)
Follow the syntax below when using SELECT INTO for temporary tables.
SELECT [column_list] INTO #temp_tablename | ##temp_tablename
[INNER | LEFT | RIGHT JOIN table1 ON join_condition1]
[INNER | LEFT | RIGHT JOIN tableN ON join_conditionN]
[WHERE condition]
[GROUP BY columns_to_group HAVING condition]
[ORDER BY sort_columns] The above syntax applies to SQL Server 2022. Let’s describe the parts specific to temporary tables:
column_list – The list of columns you want to include in the temporary table. There should be at least one column in the column list.
#temp_tablename | ##temp_tablename – The name you assign to the temporary table. You should follow the naming rules in SQL Server when giving names to temporary tables. Prefix them with # or ## to indicate local or global temporary tables.
The SELECT INTO has an ON filegroup clause where you can specify what file group to store the new table on. This does not apply to temporary tables.
This involves 2 steps:
- Create the table based on the SELECT statement
- Insert the rows from the results of the SELECT statement
If row insertion fails, the temporary table will exist, but it will be empty. If you don’t want that to happen, use explicit transactions.
SELECT INTO Temp Table Examples
Example 1 – Using SELECT INTO Temp Table to Categorize Products
The script below creates a temporary table and then shares it to get 2 different query results.
The temporary table stores individual products and their categories. Then, the next query gets the sales by product category for Q4 2022 using the temp table. Finally, yet another query uses the temp table to get product reviews and categories.
USE AdventureWorks;
GO
-- Create a temporary table to get categorized products
SELECT
a.ProductID
,a.Name as ProductName
,c.Name as ProductCategoryName
INTO #tempCategorizedProducts
FROM Production.Product a
INNER JOIN Production.ProductSubcategory b on a.ProductSubcategoryID = b.ProductSubcategoryID
INNER JOIN Production.ProductCategory c on b.ProductCategoryID = c.ProductCategoryID
-- Query product sales by category for Q4 2022
SELECT
c.ProductCategoryName
,SUM(a.TotalDue) as ProductSales
FROM Sales.SalesOrderHeader a
INNER JOIN Sales.SalesOrderDetail b ON a.SalesOrderID = b.SalesOrderID
INNER JOIN #tempCategorizedProducts c ON b.ProductID = c.ProductID
WHERE a.OrderDate BETWEEN '10/01/2022' AND '12/31/2022'
GROUP BY c.ProductCategoryName;
-- Query product reviews by product and product category for Q4 2022
SELECT
b.ProductCategoryName
,b.ProductName
,a.Rating
FROM Production.ProductReview a
INNER JOIN #tempCategorizedProducts b ON a.ProductID = b.ProductID
WHERE a.ReviewDate BETWEEN '10/01/2022' AND '12/31/2022' You may encounter scenarios that need sharing the result of a temp table. But examine the performance impact to see if a temporary table is the better alternative.
Example 2 – Changing a Complex SELECT to a Simple One with SELECT INTO Temp Table
Consider the query below with a derived table:
SELECT
d.Name as Department
,b.JobTitle
,e.LastName + ISNULL(e.Suffix,'') + ', ' + e.FirstName + ISNULL(' ' + e.MiddleName,'') as EmployeeName
,a.Rate
FROM
(
SELECT TOP 5 a.BusinessEntityID, a.Rate
FROM HumanResources.EmployeePayHistory a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
WHERE b.OrganizationNode.GetLevel() > 1
ORDER BY Rate DESC
) a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c ON a.BusinessEntityID = c.BusinessEntityID
INNER JOIN HumanResources.Department d ON c.DepartmentID = d.DepartmentID
INNER JOIN Person.Person e on a.BusinessEntityID = e.BusinessEntityID
WHERE c.EndDate IS NULL; The purpose is to get the department with employees that are not C levels with the highest pay rates. The OrganizationNode hierarchyid column takes care of that.
The query is just one SELECT statement with a derived table and several joins inside. Not a very simple query, if you ask me.
One way to simplify this query is to divide it into two queries, one of them being a temp table. Here they are:
-- First, dump the top 5 employee pay rates
SELECT TOP 5 a.BusinessEntityID, a.Rate
INTO #tempTop5Paychecks -- dump to temporary table
FROM HumanResources.EmployeePayHistory a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
WHERE b.OrganizationNode.GetLevel() > 1 -- do not include executives or C levels
ORDER BY Rate DESC
-- Get a list that includes department, job title, and employee name
SELECT
d.Name as Department
,b.JobTitle
,e.LastName + ISNULL(e.Suffix,'') + ', ' + e.FirstName + ISNULL(' ' + e.MiddleName,'') as EmployeeName
,a.Rate
FROM #tempTop5Paychecks a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c ON a.BusinessEntityID = c.BusinessEntityID
INNER JOIN HumanResources.Department d ON c.DepartmentID = d.DepartmentID
INNER JOIN Person.Person e on a.BusinessEntityID = e.BusinessEntityID
WHERE c.EndDate IS NULL; The second query becomes easier on the eye when we take away the derived table. Meanwhile, the derived table becomes the temporary table.
But the question is, will the modified query perform better than the first one? We’ll have a separate section for this, so stay tuned and read on.
Dropping Temporary Tables Made with SELECT INTO
When you create temporary tables, you won’t see them in the current database you’re working on. SQL Server puts them into TempDB. Let’s try it using the previous example:
USE AdventureWorks;
GO
SELECT
a.ProductID
,a.Name as ProductName
,c.Name as ProductCategoryName
INTO #tempCategorizedProducts
FROM Production.Product a
INNER JOIN Production.ProductSubcategory b on a.ProductSubcategoryID = b.ProductSubcategoryID
INNER JOIN Production.ProductCategory c on b.ProductCategoryID = c.ProductCategoryID Then, you will need to check the TempDB tables list. For convenience, let’s do it using dbForge Studio for SQL Server:
Note that there’s an error when trying to access the same table in SQL Server Management Studio. That happened because the temporary table is local to the session in dbForge Studio for SQL Server. Check it out below.
The temporary table will disappear when the session ends. In this case, when we disconnect dbForge Studio from our SQL Server instance, the deletion is automatic.
But you can delete the temporary table yourself using DROP TABLE:
DROP TABLE #tempCategorizedProducts We learn something good with temporary tables. Imagine using a regular physical table for temporary processing. Not only will it cause problems in a multi-user environment, but it will also clutter the database with various tables for temporary use.
Note, however, that you can’t create another temporary table with the same name in the same session. SQL Server will tell you it already exists. See the example below:
How to Avoid Slowing Down Your Database When Using SELECT INTO Temp Table
Making every query to use temporary tables is not a good idea. That’s why we reminded you to examine query performance to see if doing this is correct.
That’s what we will do in this section.
One way to examine queries is to check the logical reads. The lower the logical reads, the better. We can do this by turning ON the STATISTICS IO. Let’s use the previous query as an example.
-- Turn STATISTICS IO ON
SET STATISTICS IO ON
SELECT
d.Name as Department
,b.JobTitle
,e.LastName + ISNULL(e.Suffix,'') + ', ' + e.FirstName + ISNULL(' ' + e.MiddleName,'') as EmployeeName
,a.Rate
FROM
(
SELECT TOP 5 a.BusinessEntityID, a.Rate
FROM HumanResources.EmployeePayHistory a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
WHERE b.OrganizationNode.GetLevel() > 1
ORDER BY Rate DESC
) a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c ON a.BusinessEntityID = c.BusinessEntityID
INNER JOIN HumanResources.Department d ON c.DepartmentID = d.DepartmentID
INNER JOIN Person.Person e on a.BusinessEntityID = e.BusinessEntityID
WHERE c.EndDate IS NULL;
-- Turn STATISTICS IO OFF
SET STATISTICS IO OFF Using STATISTICS IO will add messages to SQL Server Management Studio. It will show the logical reads, scan count, and more. In dbForge Studio for SQL Server, the same messages go to the Output window. But if you have the high-end Enterprise Edition of the Studio, turning on the Query Profiling Mode is much better.
Here’s the STATISTICS IO result:
The tabular result above uses statisticsparser.com. You only need to paste the messages generated by STATISTICS IO from your GUI tool into it. Then, parse it to produce this table.
Note the individual logical reads per table for now.
Now, let’s get the STATISTICS IO after simplifying the query with temporary tables.
SET STATISTICS IO ON
-- First, dump the top 5 employee pay rates
SELECT TOP 5 a.BusinessEntityID, a.Rate
INTO #tempTop5Paychecks -- dump to temporary table
FROM HumanResources.EmployeePayHistory a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
WHERE b.OrganizationNode.GetLevel() > 1 -- do not include executives or C levels
ORDER BY Rate DESC
-- Get a list that includes department, job title, and employee name
SELECT
d.Name as Department
,b.JobTitle
,e.LastName + ISNULL(e.Suffix,'') + ', ' + e.FirstName + ISNULL(' ' + e.MiddleName,'') as EmployeeName
,a.Rate
FROM #tempTop5Paychecks a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c ON a.BusinessEntityID = c.BusinessEntityID
INNER JOIN HumanResources.Department d ON c.DepartmentID = d.DepartmentID
INNER JOIN Person.Person e on a.BusinessEntityID = e.BusinessEntityID
WHERE c.EndDate IS NULL;
SET STATISTICS IO OFF And here are the surprising results:
It turned out that using temporary tables added a bit of logical reads. Another problem is the increase in scan count. This is why you need to examine query performance before using a certain method. The temporary table added 1 logical read to the statistics. If you can live with it, fine. But experts do not stop at this.
Alternatives to Temporary Table and Derived Table
So, it turns out that using a Common Table Expression (CTE) uses the same logical reads as the first query. But the plus side is the simpler SELECT. Here’s a better alternative involving a CTE.
;WITH top5Paychecks AS
(
SELECT TOP 5 a.BusinessEntityID, a.Rate
FROM HumanResources.EmployeePayHistory a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
WHERE b.OrganizationNode.GetLevel() > 1
ORDER BY Rate DESC
)
SELECT
d.Name as Department
,b.JobTitle
,e.LastName + ISNULL(e.Suffix,'') + ', ' + e.FirstName + ISNULL(' ' + e.MiddleName,'') as EmployeeName
,a.Rate
FROM top5Paychecks a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c ON a.BusinessEntityID = c.BusinessEntityID
INNER JOIN HumanResources.Department d ON c.DepartmentID = d.DepartmentID
INNER JOIN Person.Person e on a.BusinessEntityID = e.BusinessEntityID
WHERE c.EndDate IS NULL; You might think that a simpler query like this is faster:
SELECT TOP 5
d.Name as Department
,b.JobTitle
,e.LastName + ISNULL(e.Suffix,'') + ', ' + e.FirstName + ISNULL(' ' + e.MiddleName,'') as EmployeeName
,a.Rate
FROM HumanResources.EmployeePayHistory a
INNER JOIN HumanResources.Employee b ON a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c ON a.BusinessEntityID = c.BusinessEntityID
INNER JOIN HumanResources.Department d ON c.DepartmentID = d.DepartmentID
INNER JOIN Person.Person e on a.BusinessEntityID = e.BusinessEntityID
WHERE b.OrganizationNode.GetLevel() > 1
AND c.EndDate IS NULL
ORDER BY Rate DESC Besides, there’s no derived table, CTE or temporary table, just joins. And it gives the same results. And it’s also simpler. But check the logical reads below:
It’s a lot worse than using temporary tables. The Department and Person tables have high logical reads.
So, is using temporary tables bad?
The example presented here makes it seem that way. But it doesn’t happen all the time. If you started with the last query with joins only, the temporary table fares way better.
Here are some more tips to consider:
- Think it over before dumping hundreds of thousands of rows or more into a temporary table. It uses TempDB, and each SQL Server session will have its own copy. Again, check the performance impact of this idea.
- Place your TempDB in a faster storage like a fast SSD. Do not place it in the same storage as the server operating system. TempDB is not just for temporary tables. Everything else that’s temporary also lives there.
- Index temporary tables for increased query performance.
- Do not use temporary tables if there’s a faster and simpler alternative.
- Aside from logical reads, another performance-tuning tool is the query execution plan.
- Before using SELECT INTO, check the query performance without it. The SELECT statement to query the rows should be quick too.
Takeaways in Using SELECT INTO Temp Table
Temporary tables help you avoid cluttering your database with excessive tables. They live and die in TempDB. After the session is over, SQL Server erases your local temporary tables.
Local temporary tables are visible in the same session while global temporary tables are visible to all active sessions. No matter what type, temporary tables are useful for multi-step data processing.
Using SELECT INTO is one of the ways to create a temporary table. It queries the rows first, then inserts them into the temporary table. The syntax is like using the conventional SELECT statement with an added INTO clause. The actual complexity depends on what you are trying to achieve and your database design.
Temporary tables can simplify data processing. But they may cause performance problems with inefficient use. So, practice different methods to achieve the same results.
Tuning query performance is fun. And with the right tool, you can make your queries run faster in no time. dbForge Studio for SQL Server is one of such tools, as it has the Query Profiler feature. It makes comparing logical reads and execution plans a breeze. It’s a must-have feature for meticulous SQL Server users who want fast queries. Try downloading it today and see the big difference.
Last modified: August 16, 2023
