SQL Temporary Table

---

---

In SQL, sometimes we need a table just for a short time while running a query or during a session. In such cases, we can use a Temporary Table. It helps us to store and work with data temporarily, without changing the main database tables.

The SQL Temporary Table

A Temporary Table in SQL is a special type of table that only exists during your current database session. It is created using the CREATE TEMPORARY TABLE statement and is only visible to the session (or user) that created it.

Once the session ends or the connection is closed, the table is automatically deleted. Temporary tables do not affect the actual database structure. They are useful when you want to:

• Store results in the middle of complex queries.
• Split (break) a big task into smaller steps.
• Use data temporarily without saving it permanently in the main database.

Creating Temporary Tables in SQL

To create temporary tables in SQL, you use a query similar to creating regular tables. The only difference is that instead of using the CREATE TABLE statement, you use CREATE TEMPORARY TABLE.

Syntax

Following is the basic syntax to create a temporary table in SQL:

CREATE TEMPORARY TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
  columnN datatype,
  PRIMARY KEY( one or more columns )
);

Here:

• CREATE TEMPORARY TABLE: It is the SQL command to create a temporary table.
• table_name: It is the name of the temporary table (usually prefixed).
• column1, column2, ...: These are the names of the columns to store data.
• datatype: These are the type of data each column holds, like INT, VARCHAR, DATE, etc.

Example

Let us create a temporary table named CUSTOMERS in SQL:

CREATE TEMPORARY TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

After executing the above command, a temporary table is created in the current session:

Query OK, 0 rows affected (0.03 sec)

Inserting Data into Temporary Table

We can insert data into the temporary table just like any regular table using the INSERT INTO statement.

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 );

Following is the output obtained:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

To view the data in the CUSTOMERS table, we can use the SELECT statement as shown below:

SELECT * FROM CUSTOMERS;

We get the following result:

Dropping a Temporary Table in SQL

If you want to delete the temporary table before the session ends, you can use the DROP TEMPORARY TABLE command.

Syntax

Following is the basic syntax to delete a temporary table in SQL:

DROP TEMPORARY TABLE table_name;

Example

Following query drops the temporary table CUSTOMERS created in the previous example:

DROP TEMPORARY TABLE CUSTOMERS;

We get the following output:

Query OK, 0 rows affected (0.00 sec)

This will delete the temporary table and free up memory. If you try to access it after dropping, you will get an error.

What Happens After Session Ends?

When your session or connection to the database ends, any temporary table you created is automatically deleted by the database engine. You don't need to manually drop it.

Types of Temporary Tables in SQL Server

In MySQL, temporary tables are visible only to the session that creates them. However, in Microsoft SQL Server, you can create two types of temporary tables:

• Local Temporary Tables
• Global Temporary Tables

Local Temporary Tables

A Local Temporary Table in SQL Server is accessible only within the session (or connection) that created it. It is automatically deleted when the session is closed.

If a local temporary table is created inside a stored procedure, it is dropped automatically as soon as the stored procedure finishes execution.

Syntax

To create a local temporary table in SQL Server, use a single hash (#) as a prefix to the table name, as shown below:

CREATE TABLE #table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ...
   columnN datatype,
   PRIMARY KEY (one or more columns)
);

Example

Let us create a local temporary table named #CUSTOMERS in SQL Server:

CREATE TABLE #CUSTOMERS (
   ID       INT NOT NULL,
   NAME     VARCHAR(20) NOT NULL,
   AGE      INT NOT NULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18, 2),
   PRIMARY KEY (ID)
);

We will get the output as shown below:

Commands completed successfully.

This table will be accessible only in the current SQL session and will be automatically removed when the session ends or the stored procedure (if used inside one) finishes execution.

Global Temporary Tables

A Global Temporary Table is accessible to all sessions and connections on the SQL Server. However, it remains available only as long as at least one session is actively using it. Once the last session referencing the table is closed, the table is automatically dropped.

Syntax

To create a global temporary table, use a double hash (##) prefix before the table name, as shown below:

CREATE TABLE ##table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ...
   columnN datatype,
   PRIMARY KEY (one or more columns)
);

Example

Let us create a global temporary table named ##Buyers in SQL Server:

CREATE TABLE ##Buyers (
   ID       INT NOT NULL,
   NAME     VARCHAR(20) NOT NULL,
   AGE      INT NOT NULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18, 2),
   PRIMARY KEY (ID)
);

We will get the output as shown below:

Commands completed successfully.

This table can be accessed by all sessions and will only be deleted when the last session using it is closed.

Differences Between Local and Global Temporary Tables

Following are the major differences between Local and Global Temporay tables in SQL:

Temporary Table vs Permanent Table

The table below shows the differences between temporary and permanent tables:

Important Points About Temporary Tables

Following are some simple things to remember about temporary tables in SQL:

• They are useful for storing temporary or intermediate data.
• Created using the CREATE TEMPORARY TABLE command.
• They are automatically removed when the session ends.
• You can use them like regular tables insert, update, or select data.
• Each session has its own temporary tables, other users can't see them.