Introduction to Temporary Tables in SQL Server

A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. 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. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.

Preparing the Data

Let’s first prepare some dummy data. We will use this data to create 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 ‘schooldb’. In this database, a table called ‘student’ is created and some dummy data added into the table.

Creating A Temporary Table

There are two methods of creating temporary tables.

Method 1

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 the male student 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. Here we created a temporary table “#MaleStudents” which stores the name, age, and gender of all the male student records from 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, to see where this table exists; 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. This is a unique identifier. Multiple database connections can create temporary tables with the same name, therefore to differentiate between the temporary tables created by different connections, 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. Therefore, in the same query window that created the “#MaleStudents” table, execute the following query.

SELECT * FROM #MaleStudents

Since, the #MaleStudents table contains the name, age, and gender of all the male students. The above query will fetch following results.

To create a new connection you can simply open a new query window in “SQL Server Management Studio”. Now, keep the previous connection open and create another “MaleStudents” table using method 2 in a new query window (new connection).

Method 2

The second method is similar to creating normal tables. Take a look at the following query. Here again, we shall create #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.

Continue reading here https://goo.gl/1s3vhH and you will also learn about:

• Global Temporary Tables
• Deleting a Temporary Table
• Automatic Deletion
• Manual Table Deletion
• Temporary Tables and Stored Procedures

Thanks for reading!