SQL TEMP Tables

As the name suggests, Temporary Tables are temporary in nature but act as full features table until the connection is closed. The main difference between a permanent and a temporary table is that table definition and data stored is persistent in case normal permanent table, on the other hand temporary tables are session specific i.e. you can use you it until the session is connected. Once the session to the database is closed, the table drops.

Temporary tables can be created in the most of the RDBMS like SQL Server, MySQL, Oracle etc. Temporary tables are very useful when need to store and process intermediate data.

SQL Temp Table Syntax

The basic syntax of creating Temporary table is as follows.

CREATE TABLE #tablename (
column1 datatype1,
column2 datatype2,
......
column(n) datatype(n)
)

Here,

#tablename – The name of the Temporary table you want to create.
column(n) – The name of table column.
datatype(n) – The datatype of the column.

SQL Temp Table Example

To CREATE Temporary Table:

CREATE TABLE #EmployeeData ( empid INT, name VARCHAR(50) );

To Insert Values into Temporary Table:

INSERT INTO #EmployeeData values (01,'Sagar Sharma'), (02, 'Shankar Kumar');

To Select Values from the Temporary Table:

SELECT * FROM #EmployeeData;

Result:

There are two types of employee table based on their behavior and scope.

1. Local Temp Table
2. Global Temp Table

Local Temp Table

A Local Temporary table is local for the session that means the table only available to the session that has created it. Once the connection
is closed the temporary table automatically deleted. In order to create a local temporary table, a single “#” is used as the prefix of a
table name.

Example

CREATE TABLE #usertable ( 
userid int, 
username varchar (50), 
user address varchar (150) 
)

To drop a temporary table manually you can the following statement.

DROP TABLE #usertable;

Global Temp Table

A Global Temporary table is temporary in nature but available across the users connection referencing the table until the last connection is dropped. To create a Global Temporary table, double has “##” need to be added before the table name.

Example

CREATE TABLE ##usertable ( 
userid int, 
username varchar (50), 
user address varchar (150) 
)