What is Temporary Table?

Temporary tables are same as permanent tables. Temporary tables are created in temporary database. Temporary Tables are deleted automatically when they are not used for long time.

We should make a note that table name begin with '#' is a temporary table and stored in test database.

SQL Server and Oracle databases uses the temporary tables.

There are two types of the temporary table based on their visibility to the users or developers. Those are -

• Global Temporary Tables
• Local Temporary Tables

Local Temporary Table

CREATE TABLE statement used to create Local Temporary table followed by table name prefix with single number sign '#'. Local Temporary table was stored in test database. Local Temporary table visible to the session where the table was created first. If user want to use Local Temporary table in another session, it won't be possible in SQL.

We can run the query on same Local temporary table multiple times until session was closed. Local Temporary Table has a random number in the end of the table name.

If Local Temporary Table was created in Stored procedure, it deletes automatically when stored procedure was closed. Local Temporary Table visible to one session only, if the session closed, Local Temporary Table was automatically get deleted.

CREATE TABLE #table_name ( 
     column1 data_type [ NULL | NOT NULL ],
     column2 datatype [ NULL | NOT NULL ],
        ……………………..                
     columnN data_type [NULL | NOT NULL] 
  ); 

Scenario - Create Local Temporary Table.

Requirement – Create Local Temporary Table #employee_details with the two columns named emp_id, emp_name. The query was as follows –

CREATE TABLE #employee_details (
        emp_id int  not null,
        emp_name varchar (20) 
  ); 

By execute above query, a Local Temporary Table called #employee_details was created.

Scenario – Inserting values to Local temporary table.

Requirement –Insert rows to #employee_details table. The query was as follows –

INSERT INTO  # Employee_details VALUES (001, Employee1),
                                   (002, Employee2); 

By execute above query, we can insert rows in #employee_details table. The output was as follows –

Scenario – Getting data from Local Temporary Table.

Requirement – Get the data from #employee_details table. The query was as follows -

SELECT * FROM #employee_details;  

By executing above query, we can get data from #employee_details table. The output was shown as below -

Scenario – Deleting Local Temporary table explicitly.

Requirement – Delete #employee_details table. The query was as follows -

 DROP TABLE #employee_details; 

By executing above query, we can delete #employee_details table explicitly.

Scenario – Verifying deleted Local Temporary table.

Requirement – Verify whether table #employee_details table was dropped or not. The query was as follows -

SELECT * FROM #employee_details; 

By executing above query, we will get "Invalid Object table not found" error message if the table gets deleted successfully. Otherwise, table gets displayed.