A temporary table in SQL Server, as the name suggests, is a database table that exists on the database server temporarily. It stores a subset of the normal table data for a certain period of time.
Temporary tables are particularly useful when you have a large number of records in a table and need to interact with small subsets of those records constantly. In such cases, instead of filtering the data again and again to fetch the subset, you can filter the data once, and store it in a temporary table. Then, you can execute queries on that temporary table.
Temporary tables are stored inside “tempdb,” which is a system database. Now, let’s take a look at how you can use temporary data in a simple scenario.
Preparing the Data
First, we prepare some dummy data. We use them to create SQL Server temporary tables.
Run the following script on your database server:
CREATE DATABASE schooldb CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL, age INT NOT NULL, total_score INT NOT NULL, ) INSERT INTO student VALUES (1, 'Jolly', 'Female', 20, 500), (2, 'Jon', 'Male', 22, 545), (3, 'Sara', 'Female', 25, 600), (4, 'Laura', 'Female', 18, 400), (5, 'Alan', 'Male', 20, 500), (6, 'Kate', 'Female', 22, 500), (7, 'Joseph', 'Male', 18, 643), (8, 'Mice', 'Male', 23, 543), (9, 'Wise', 'Male', 21, 499), (10, 'Elis', 'Female', 27, 400);
The above SQL script creates a database called ‘schooldb’. In this database, a table called ‘student’ is created, and some dummy data added to the table.
Creating A Temporary Table
There are two methods for creating SQL Server temporary tables.
Creating a Temporary Table using SELECT INTO statement
The simplest way of creating a temporary table is by using an INTO statement within a SELECT query.
Let’s create a temporary table that contains the name, age, and gender of all male students’ records from the student table.
USE schooldb; SELECT name, age, gender INTO #MaleStudents FROM student WHERE gender = 'Male'
Take a look at the above query. We created a temporary table “#MaleStudents,” which stores names, ages, and genders of all male students’ records from the student table. To define a temporary table, we use the INTO statement after the SELECT statement. The name of a temporary table must start with a hash (#).
Now, we want to view the table location. We go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”.
You will see your temporary table name along with the identifier. Take a look at the following figure:
You must be wondering about the “000000000006” at the end of the table name. It is a unique identifier. Multiple database connections can create temporary tables with the same name. Therefore, to differentiate between the temporary tables created this way, the database server automatically appends this unique identifier at the end.
You can perform operations on the temporary table via the same connection that created it. In the same query window that created the “#MaleStudents” table, execute the following query:
SELECT * FROM #MaleStudents
The #MaleStudents table contains names, ages, and genders of all male students. The above query will fetch the following results:
To create a new connection, simply open a new query window in “SQL Server Management Studio”. Then, keep the previous connection open and create another “MaleStudents” table, using the second method in a new query window (new connection). We explore this second method below.
Creating a Temporary Table using CREATE TABLE Command
The second method is similar to creating normal tables. Take a look at the following query. Again, we create the #MaleStudents temporary table. Remember, this query must be executed by a new connection.
USE schooldb; CREATE TABLE #MaleStudents ( name VARCHAR(50), age int, gender VARCHAR (50) ) INSERT INTO #MaleStudents SELECT name, age, gender FROM student WHERE gender = 'Male'
Now, if you execute the above query, you should see two #MaleStudents temporary tables with different unique identifiers inside the tempdb. This is because these two tables have been created by two different connections. Take a look at the following screenshot:
Temporary tables can be of different types. The most frequently mentioned are Global temporary tables and Local temporary tables.
Global Temporary Tables
It is pertinent to mention here that a temporary table is only accessible to the connection that created that temporary table. It is not accessible to other connections. However, we can create temporary tables that are accessible to all the open connections. Such temporary tables are called global temporary tables. The name of the global temporary table starts with a double hash symbol (##).
Let’s create a global temporary table that contains records of all female students from the student table.
USE schooldb; SELECT name, age, gender INTO ##FemaleStudents FROM student WHERE gender = 'Female'
Now, you can access the ##FemaleStudents table from any of the open connections.
Local temporary tables
The names of these tables begin with #. The full length of this name must be shorter than 116 symbols. This type is more secure than “global,” as it is only available for the owning process. It is impossible to use them in views, and triggers won’t get associated with the Local temporary tables. When the session or procedure finishes, the Local temporary table is dropped.
Dropping a temporary table
There are two ways to delete temporary tables in SQL Server: Automatic Deletion and Manual Deletion.
A temporary table is automatically deleted when the connection that created the table is closed. Alternatively, when you close the query window that created the temporary table. Without saving changes, the table will be closed. If a connection is executing some queries on the global table, those queries must be completed before the global table deletion.
Manual Table Deletion
You can manually delete a table without closing a connection. For that, use the DROP TABLE statement. However, remember that the statement has to be executed by the connection that actually created the table.
Take a look at the following query:
DROP TABLE #MaleStudents
This is similar to deleting a normal table.
Using Temporary Tables in Stored Procedure
Earlier we learned that a temporary table can only be accessed locally inside the connection that created it. There is one exception to that rule. When you are creating stored procedures, you can access temporary tables in other connections as well.
Let’s create two stored procedures using two different connections. The first stored procedure will insert the data into the #MaleStudents table, while the second one will select the data from the table.
Create a new connection. If you are using SQL Server Management Studio, you can do this by opening a new query window. Execute the following SQL script in the new query window:
Create Procedure spInsertStudent (@Name Varchar(50), @Age int, @Gender Varchar(50)) As Begin Insert Into #MaleStudents Values (@Name, @Age, @Gender) End
We’ve now created a stored procedure that inserts a record into the #MaleStudent temporary table. Note that this connection did not create the #MaleStudent, yet we are accessing it by inserting a record into it. The reason is, creating a stored procedure lets you access temporary tables from a connection other than the one that created the table. If you execute the above query, you will see that SQL Server will not throw any error.
Similarly, open a new connection and create the following stored procedure in it:
CREATE PROCEDURE spListStudent AS BEGIN SELECT * FROM #MaleStudents ORDER BY name END
The above-stored procedure selects all the records from the #MaleStudents temporary table. Here again, we are accessing a temporary table from inside the connection that did not create the table.
Now, the tricky part comes. Though you can access a temporary table inside another connection when creating a stored procedure, you cannot access a temporary table when “executing” a stored procedure inside another connection. To execute a stored procedure that accesses a temporary table, you have to be inside the connection that created the temporary table.
Therefore, execute the following queries inside the connection that created the #MaleStudents table:
EXECUTE spInsertStudent Bradley, 45, Male Execute spListStudent
The first stored procedure inserts a new student record with the name: Bradley, age: 45, and gender: Male into the #MaleStudents table.
The second stored procedure selects all the records from the #MaleStudents table in the ascending order of name. The output of the above-stored procedures will be:
You can clearly see our newly inserted record in the selected records above.
- Pivoting, Unpivoting, and Splitting Columns in Power BI Query Editor - July 7, 2020
- Formatting Data in Power BI Desktop Visualizations - July 3, 2020
- Implementing Hierarchies in Power BI Desktop Visualizations - July 2, 2020