Back to ADVANCED DATABASE CONCEPTS - STORED PROCEDURES, VIEWS, TEMP TABLES

Temp Tables

10 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Temp Tables Overview — Temp tables are introduced as session-bound storage structures that allow multiple downstream reads unlike single-use CTEs.
Creating and Inserting Data — The hash prefix syntax is introduced alongside basic DDL and row-level value insertion techniques.
Staging Subsets from Large Tables — Query results are inserted directly into temp tables to avoid repeated full table scans on large datasets.
Aggregations and Downstream Reuse — A complex multi-table join with group by aggregations is staged to show processing time reductions.
DROP TABLE and Idempotency — Conditional drop table syntax is implemented to prevent execution errors during repeated stored procedure runs.

PDF notes

Bottom line: You will create, populate, and manage session-scoped SQL temporary tables using the hash prefix syntax. You will optimize analytical workflows by staging intermediate query results and pre-calculated aggregations, cutting query runtimes and preventing repetitive multi-table joins.

This lesson on Temp Tables is hands-on and example-driven. You will create, populate, and manage session-scoped SQL temporary tables using the hash prefix syntax. You will optimize analytical workflows by staging intermediate query results and pre-calculated aggregations, cutting query runtimes and preventing repetitive multi-table joins.

What You'll Be Able To Do

Create session-scoped temporary tables using explicit schema definitions and the # prefix.
Insert single records and multi-row query subsets into temporary tables.
Stage complex join and aggregation outputs into temporary tables for multi-query reuse.
Implement DROP TABLE IF EXISTS guards to prevent object collision errors during script reruns.

Detailed Concept Walkthrough

1. Temporary Table Creation and Syntax

Temporary tables are physically materialized tables that exist exclusively within the lifetime of an active database connection. They share standard table DDL syntax except for a leading hash prefix.

Mechanism: Create a temp table using CREATE TABLE #tablename followed by standard column specifications. The # prefix informs the database engine to isolate the table to the current session.
Under the Hood: Unlike persistent user tables, temporary tables are allocated in system temporary storage (such as tempdb) and automatically purge when the user session disconnects.
Syntax Rule: Column definitions must adhere to standard SQL data types, such as INT and VARCHAR(n), matching the data structures intended for staging.

-- Create a session-scoped temporary table
CREATE TABLE #temp_employee (
    EmployeeID INT,
    JobTitle VARCHAR(100),
    Salary INT
);

Key Takeaway: A single # prefix marks a table as temporary and isolates it to your active database session.

2. Populating and Staging Query Subsets

Temp tables can ingest individual row values or capture entire subsets directly from base tables using insert-select statements.

Mechanism: Populate tables manually using INSERT INTO #table VALUES (...) or programmatically pipe query results using INSERT INTO #table SELECT ....
Under the Hood: When populating via SELECT, the database engine materializes the query result set into temporary storage, decoupling subsequent operations from the source tables.
Best Practice: Use INSERT INTO #table SELECT to isolate smaller working subsets from massive billion-row base tables before running downstream analytics.

-- Insert an explicit single row
INSERT INTO #temp_employee VALUES (101, 'HR', 45000);

-- Stage a subset from a large base table
INSERT INTO #temp_employee
SELECT EmployeeID, JobTitle, Salary
FROM EmployeeSalary;

Key Takeaway: Staging data from large tables into temporary tables decouples analytics from expensive source table scans.

3. Intermediate Aggregations and Performance Optimization

Persisting complex multi-table joins and aggregations into temp tables prevents redundant computation across multiple downstream analytical queries.

Mechanism: Compute resource-intensive joins and GROUP BY aggregations once, writing the output directly into a dedicated temp table.
Under the Hood: Unlike CTEs and subqueries that re-execute their underlying logic each time they are referenced, temp tables store materialized results on disk/memory for fast, repeated querying.
Best Practice: Target multi-table joins with grouping functions for temporary materialization whenever downstream tasks require filtering or slicing the same aggregate metrics.

-- Define temp table for aggregated metrics
CREATE TABLE #temp_Employee2 (
    JobTitle VARCHAR(50),
    EmployeesPerJob INT,
    AvgAge INT,
    AvgSalary INT
);

-- Stage complex join and aggregation results
INSERT INTO #temp_Employee2
SELECT JobTitle, COUNT(JobTitle), AVG(Age), AVG(Salary)
FROM EmployeeDemographics emp
JOIN EmployeeSalary sal ON emp.EmployeeID = sal.EmployeeID
GROUP BY JobTitle;

-- Query materialized metrics without re-joining
SELECT * FROM #temp_Employee2 WHERE AvgSalary > 50000;

Key Takeaway: Temp tables materialize expensive joins and aggregations once, allowing fast repeated querying without re-executing calculations.

4. Idempotent Execution and Stored Procedures

Because temp tables persist until a session closes, re-executing creation scripts will fail unless existing objects are conditionally dropped first.

Mechanism: Place DROP TABLE IF EXISTS #tablename prior to CREATE TABLE statements to eliminate collision errors when scripts run repeatedly.
Under the Hood: If a temp table exists in the connection scope, attempting a new CREATE TABLE throws an object-already-exists error because the metadata entry is still active.
Best Practice: Always guard temp table definitions with conditional drop statements inside stored procedures and iterative development scripts to guarantee idempotency.

-- Ensure script rerunability by dropping prior instances
DROP TABLE IF EXISTS #temp_Employee2;

CREATE TABLE #temp_Employee2 (
    JobTitle VARCHAR(50),
    EmployeesPerJob INT,
    AvgAge INT,
    AvgSalary INT
);

Key Takeaway: Guarding creation statements with DROP TABLE IF EXISTS ensures stored procedures and scripts execute idempotently.

Topics Covered in Temp Tables

Temp Tables Overview (0:00 - 0:35) — Temp tables are introduced as session-bound storage structures that allow multiple downstream reads unlike single-use CTEs.
Creating and Inserting Data (0:35 - 1:25) — The hash prefix syntax is introduced alongside basic DDL and row-level value insertion techniques.
Staging Subsets from Large Tables (1:25 - 2:15) — Query results are inserted directly into temp tables to avoid repeated full table scans on large datasets.
Aggregations and Downstream Reuse (2:15 - 3:40) — A complex multi-table join with group by aggregations is staged to show processing time reductions.
DROP TABLE and Idempotency (3:40 - 4:45) — Conditional drop table syntax is implemented to prevent execution errors during repeated stored procedure runs.

SQL Cheat Sheet

CREATE TABLE #table_name (...) — Creates a session-scoped temporary table schema

CREATE TABLE #temp_employee (EmployeeID INT, JobTitle VARCHAR(100), Salary INT);

INSERT INTO #table_name VALUES (...) — Inserts explicit row values into a temporary table
```
INSERT INTO #temp_employee VALUES (101, 'HR', 45000);
```

INSERT INTO #table_name SELECT ... — Populates a temp table with query results

INSERT INTO #temp_employee SELECT EmployeeID, JobTitle, Salary FROM EmployeeSalary;

SELECT ... FROM #table_name — Reads stored data from an active temporary table

SELECT JobTitle, AvgSalary FROM #temp_Employee2 WHERE EmployeesPerJob > 5;

DROP TABLE IF EXISTS #table_name — Deletes temporary table if it already exists
```
DROP TABLE IF EXISTS #temp_Employee2;
```

Comparison Table

Aspect	Temporary Table (#temp)	CTE / Subquery
Reusability	Queried multiple times across a session	Evaluated once per statement
Storage	Materialized in temporary storage	In-memory during single execution
Performance Impact	Calculates once; reduces repeat join overhead	Recomputes underlying logic every reference
Script Idempotency	Requires DROP TABLE handling on reruns	No object cleanup required

Common Pitfalls

Mistake: Rerunning creation scripts without dropping previous instances causes table already exists errors. Avoid: Prepend every temp table creation with DROP TABLE IF EXISTS.
Mistake: Using CTEs when repeatedly referencing an expensive multi-table join. Avoid: Stage intermediate join results into a temporary table once.
Mistake: Omitting the hash prefix when defining temporary tables. Avoid: Always prefix session table names with a pound sign like #temp_table.
Mistake: Expecting temporary tables to persist after disconnecting the session. Avoid: Treat temporary tables as connection-bound scratchpads and write permanent data to base tables.

FAQs

What is the primary difference between a CTE and a temporary table? A CTE is scoped strictly to a single statement and recalculates upon every reference, whereas a temporary table materializes data across multiple queries within a session.
Why should I stage data into a temp table instead of querying base tables directly? Staging filtered subsets or pre-aggregated joins eliminates the processing overhead of repeatedly scanning billion-row tables and executing complex joins.
Where do temporary tables live inside the database? Temporary tables are stored in temporary system storage (such as tempdb in SQL Server) rather than within the permanent database schema.
Why does running my stored procedure a second time throw a collision error? The temporary table created during the first run remains in the active session; adding DROP TABLE IF EXISTS resolves the collision.

Common pitfalls

Mistake: Rerunning creation scripts without dropping previous instances causes table already exists errors. Avoid: Prepend every temp table creation with DROP TABLE IF EXISTS.
Mistake: Using CTEs when repeatedly referencing an expensive multi-table join. Avoid: Stage intermediate join results into a temporary table once.
Mistake: Omitting the hash prefix when defining temporary tables. Avoid: Always prefix session table names with a pound sign like #temp_table.
Mistake: Expecting temporary tables to persist after disconnecting the session. Avoid: Treat temporary tables as connection-bound scratchpads and write permanent data to base tables.

What's going on, everybody? Welcome back to another SQL tutorial. Today we're looking at temp tables, and if you can guess it based off of the name, they're kind of like temporary tables, and we create them very much the same way. We're going to do CREATE TABLE. Um it's just a little bit different, and you can hit off of this temp table multiple times, which you cannot do with something like a CTE or a subquery where you can only use it one time or with a subquery you need to write it multiple times within a query. And so these temp tables are extremely useful. I'm going to kind of talk about how you can use them as we're going uh throughout this video. But let's get started right away with actually creating one, looking at it, inserting some data, and and and kind of showing you how temp tables work and what we can do with them. So uh we're going to start off with CREATE TABLE, much like uh a regular table is created. The only difference is we're going to do this pound sign and then we're going to do temp_ employee. Uh so literally the only difference between a regular table and a temp table is this right here at the very beginning, this this pound sign. So uh let's just start by doing EmployeeID, we'll make that an integer, we'll do JobTitle and we'll make that a VARCHAR(100), and then we'll do Salary, and let's make that an integer. And so now we have our temp table. Uh let's go ahead and create it. So now we have our temp table created and so we can look at it really quick. So let's select everything from and we'll do temp_employee. So let's take a look. It's completely empty um and we can insert data very much the same way we'd insert data into a regular table. So let's start doing that. Let's do INSERT INTO and we'll do temp_employee and we'll do VALUES and let's just do something really quick cuz I'm going to get to a little bit more interesting stuff in a second. Oops. So we'll make this person HR uh as their job title and then for salary we'll give them 45,000 and close that off. So let's run this, and let's select everything again and see what's in there. Perfect. So we were able to insert data into this temp table, and again, we we don't have to create this every single time we um or we don't have to run this every single time we need to hit off of it like we did a CTE, if you watched my previous video. In this one, we can just run it, and it sits there, and so uh again, it feels very much like a real table. And I'm going to get to a little bit of the nuances of of the in the differences between a regular table and a temp table in a second. But let's really quickly, um we want more data in there. You don't have to just um do it value by value, we can also just do um uh where we select all of the data from a specific table and insert that into a temp table. And that is really quickly you know, how I do it most of the time. Most of the time, I'm not inserting values. Um I am you know, taking a large table and taking a subset of that and then sticking it into a temp table. So let's look at this really quick and run that. So now we took all of the data from EmployeeSalary and then we just stuck it into this table. And really quickly, this is one of the big uses of a temp table. We had Let Let's say, for example, that this EmployeeSalary table had a billion rows or or or just an extremely large number and we were trying to uh you know, hit a somewhat complex query off of it, where we're using joins and we're using uh maybe some window functions or different things, you know, it would take a very long time to hit off of this. But what we can do is we could insert that data into this temp table and then we can hit off the temp table and it already has that sub uh that sub-section of data that we're wanting to use for all of our later queries. So really quickly, that's kind of um kind of a use case for that. So let's go down here. We're going to kind of create another one, and this one's going to be a little bit more advanced and a little bit of how I would actually use a temp table. Above was just kind of showing the basic syntax, how you kind of put data into it, you know, kind of how it's used. Now I'm going to show you kind of how I would actually use it. So let's do CREATE TABLE. Uh let's do temp—oops. CREATE TABLE. Uh let's do temp _Employee2. And then let's do open parenthesis and we'll do JobTitle and we'll make that a VARCHAR (50). And then we can do Employees PerJob, we'll make that an integer. Now we need AvgAge, make that an integer, and the very last one will be Avg Salary, and make that an integer as well. And let's run this. Oops. So we have our second table. Now we want to insert data into this one, so we're just going to do INSERT INTO and we'll do temp_Employee2. And for this one, I'm going to take a query that we used in a previous video, and so I'm just going to copy and paste that to save time uh and then we'll keep on moving from there. All right, so I'm just going to paste that in. We will run this and really all it's doing is from this from these tables it's taking the job title, we're getting a count on the job title, average age, average salary, and that is it. Um so let's see if that worked, which it looks like it did, but you know, let's actually take a look at the data. And so now we have this subsection of data from this join above, and what this is going to do is whenever we want to run this, we don't have to run it on these two tables and create the join and then do the calculations, which takes time. What it's going to do is it's going to take this these exact values and place this into this temporary table, and if we wanted to run further calculations on these values, we can easily do that in a fraction of the time instead of having to run this every single time, which will take up so much uh uh processing power. And it will reduce your runtime dramatically when you're placing this data in this temp table and hitting off of that instead of all these joins and everything above. Uh a lot of times these temp tables are used in stored procedures. Now, if you haven't learned about stored procedures or used stored procedures at all, you know, that's okay. I still want to show you something that might be useful um although this is used a ton in stored procedures. So for example, let's say we have a stored procedure set up. We run the stored procedure and we get an output, and you know, we for whatever reason want to run it again, and when we run it again uh we get this error. And you know, this temp table lives somewhere. It It It doesn't live in an actual in the actual database, uh but it lives somewhere and so when we run it again, we get an error because there's already a temp table created. One trick or one little tip that I would give is doing something like this, saying DROP TABLE—oops, I don't know why I did so many spaces. DROP TABLE IF EXISTS, and we'll do temp_Employee2, just like that. Now, what this is going to do is when you're running that stored procedure over and over and over again, you're getting a error or whatever, for whatever reason you need to run it multiple times, every time that you run it, it's going to encounter this. And so if that already exists, it is going to delete that table and then allow you to create it again. And this is just a really good thing to do. So now if you see down below, I can run this time and time and time again, and it is going to work every single time because it is checking to see if that exists, and if it does, it deletes it, and then I can create again. And so that is just uh a helpful tip if you're going to try to use this. I highly recommend adding that to your query just to make sure things run smoothly. I know there is a lot more that can go into temp tables, a lot more of the technical aspects or the DBA stuff. Um obviously, I just want to teach you how to use it and what you might use it for and how to actually write it out. But you know, there are a lot more things that you can do research on about processing speed and storage. But unless you are something like a DBA, you probably don't need to worry about those things. And so if you are a DBA, I do recommend looking into those things, making sure you understand how that works, how this data is stored uh so that when people use them or you are using them, you know what's going on in the background. But for getting up and running with temp tables, I hope that this was helpful. Thank you guys so much for watching. I really appreciate it. If you like this video, be sure to like and subscribe below and I'll see you in the next video.

Frequently asked questions

What is the primary difference between a CTE and a temporary table?

A CTE is scoped strictly to a single statement and recalculates upon every reference, whereas a temporary table materializes data across multiple queries within a session.

Why should I stage data into a temp table instead of querying base tables directly?

Staging filtered subsets or pre-aggregated joins eliminates the processing overhead of repeatedly scanning billion-row tables and executing complex joins.

Where do temporary tables live inside the database?

Temporary tables are stored in temporary system storage (such as tempdb in SQL Server) rather than within the permanent database schema.

Why does running my stored procedure a second time throw a collision error?

The temporary table created during the first run remains in the active session; adding `DROP TABLE IF EXISTS` resolves the collision.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.