SQL Temporary Table

Updated March 10, 2023

Introduction to SQL Temporary Table

Temporary tables in SQL server are similar to permanent database tables that are used for storing intermediate data records. These temporary tables, as the name suggests, exist temporarily on the server. They get deleted once the last connection to the server is closed. Temporary tables are very useful in scenarios when we have a large number of rows in a permanent database table and we have to frequently use some rows of this table. We can select those specific rows which we need again and again from the permanent table into a temporary table and run queries on it more efficiently.

Syntax and parameters:

A temporary table can be created in two ways, one creates the table first and then inserts values in it. Similar to normal tables. Second, creates it while selecting records from a permanent table. Here is the basic syntax for creating temporary tables using both methods.

Case 1: Creating a temporary table using CREATE TABLE statement

CREATE TABLE {# | ##} temp_table_name (
column_name_1 data type CONSTRAINT,
column_name_2 data type CONSTRAINT,
.
.
.
column_name_n data type CONSTRAINT,
);

Case 2: Creating a temporary table using SELECT INTO statement

SELECT
columns_to_be_selected
INTO
{ # | ##} temp_table_name
FROM
table_name;

The parameters used in the above-mentioned syntaxes are as follows :

{ # | ##} : Single ‘#’ or double ‘##’ symbols indicate that a local temporary table or a global temporary table has to be created. Specify ‘#’ or ‘##’ symbols accordingly.

• temp_table_name: Name of the temporary table that has to be created.
• column_name1, … column_name_n: The columns that have to be created in the temporary table along with their data types and constraints if any.
• columns_to_be_selected : The columns that have to be selected from the regular table “table_name” and created and stored in a temporary table.

Having learnd the syntax and parameters used for creating a temporary table.

Examples

Let us try a few examples to illustrate the topic further.

Example #1

Create a temporary table called studentTemp having roll_no, student_name, degree_major, degree_year, and society as field names.

CREATE TABLE #studentTemp(
roll_no int NOT NULL PRIMARY KEY,
student_name varchar(255) NULL,
degree_major varchar(255) NOT NULL,
degree_year varchar(255) NULL,
society varchar(255) NULL);

Output:

We have used the same syntax for creating temporary tables using the CREATE TABLE statement that has been discussed above, for creating studentTemp.

The command completed successfully. The temporary table can be seen from the object explorer in the tempdb under Temporary Tables header.

Having created the said table, let’s insert a few records in it from a regular table called “students”. We have used INSERT INTO and SELECT FROM statements.

INSERT INTO #studentTemp
SELECT
roll_no,
student_name,
degree_major,
degree_year ,
society
FROM
dbo.students;

Output:

The data in the “studentTemp” after population looks something as follows :

SELECT * FROM #studentTemp;

Output:

We can query temporary tables similar to regular tables. Here is a simple SELECT statement to illustrate it.

SELECT student_name
FROM #studentTemp
WHERE degree_year = 'IV' AND society = 'Dramatics';

Output:

Example #2

Create a temporary table called “studentsTemp2” using SELECT INTO statement.

SELECT roll_no,
student_name,
degree_year,
degree_major,
society
INTO
#studentsTemp2
FROM students
WHERE degree_year = 'III';

Output:

The query executed successfully while affecting 2 rows in the students’ Temp2. The newly created table can be seen from the object explorer as mentioned in the previous example.

Working with Temporary Tables

It is exactly similar to regular tables in SQL databases.

(i) Fetching records from a temporary table

SELECT * FROM #studentsTemp2;

Output:

SELECT * FROM #studentsTemp2
WHERE student_name LIKE '%Green%';

Output:

(ii) Modifying an existing temporary table

ALTER TABLE #studentsTemp2
ADD home_town VARCHAR(50);

Output:

We have added a new column to studentsTemp2 table. The newly created column can be seen in the image below.

SELECT * FROM #studentsTemp2;

Output:

(iii) Updating a column value in temporary table

Let’s update the value of home_town column for roll_no ‘3’.

UPDATE #studentsTemp2
SET home_town = 'New York'
WHERE roll_no = '3';

Output:

The said column has been successfully updated.

SELECT * FROM #studentsTemp2
WHERE roll_no = '3';

Output:

(iv) Inserting a new row in temporary table

INSERT INTO #studentsTemp2
([roll_no]
,[student_name]
,[degree_year]
,[degree_major]
,[society]
,[home_town])
VALUES
(3,'Mohini Agarwal','III','Physics','Music','New Delhi')
GO

Output:

The query returned successfully. The newly inserted row can be seen using a SELECT statement.

SELECT * FROM #studentsTemp2
WHERE roll_no = '3';

Output:

Deleting a record from a temporary table

DELETE FROM #studentsTemp2
WHERE degree_major = 'Computer Science Engineering';

Output:

The query returned successfully.

SELECT * FROM #studentsTemp2;

Output:

From the above image, we can see that the deleted row is no longer in the table.

Deleting an existing temporary table

DROP TABLE #studentsTemp2;

Output:

The command executed successfully. The table is no longer in the database.

SELECT * FROM #studentsTemp2;

Output:

Types of Temporary Table in SQL

There are basically two types of temporary tables in SQL, namely Local and Global. The Local temporary tables are visible to only the database user that has created it, during the same session of database server. Such tables get automatically deleted once the user disconnects the database server or when his session ends. On the other hand Global temporary tables are visible to all the users created in the database server. Such tables get deleted only after all the users have disconnected from the database server.

(i) Creating a local temporary table

CREATE TABLE #localTempTable
(
id INT,
name varchar(30)
);

Output:

(ii) Creating a global temporary table

CREATE TABLE ##GlobalTempTable
(
id INT,
name varchar(30)
);

Output:

Both the tables are present in tempdb under temporary tables.

Let’s try changing the user and see if we can still access these tables.

SELECT * FROM ##GlobalTempTable;

Output:

The GlobalTempTable being global in nature is accessible to other users as well.

But the localTempTable is not accessible to other users.

SELECT * FROM #localTempTable;

Output:

Conclusion

Temporary tables in SQL are similar to regular tables but are temporary in nature. They get deleted once the user disconnects from the server.

Recommended Articles

We hope that this EDUCBA information on “SQL Temporary Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. SQL Select Top
2. SQL Cluster
3. SQL Except Select
4. SQL DROP TRIGGER