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

What is Global Temporary Table?

Temporary Table can gets created in any session or out of the stored procedure. These tables are available globally. By using CREATE TABLE Statement with the table name prefixed with a double number sign (##) Global Temporary Table created.

Characteristics

• Global Temporary table can access from all sessions of SQL Server until the server reset the connections.
• We can create Global Temporary table in any session of the SQL Server.
• We can drop Global Temporary table in any session of the SQL Server.
• Global Temporary table visible to all sessions of the SQL Server.
• Global Temporary table name has no random number.
• Global Temporary table has double number sign (##) prefix.

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

Scenario – Creating Global Temporary Table

Requirement – Create a global temporary table employee_details with two columns emp_id and emp_name. The query was as follows –

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

By executing above query, we can create Global Temporary Table employee_details.

Scenario – Inserting values Global Temporary Table

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

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

By executing above query, we can insert values in employee_details Global Temporary Table. The output was as follows –

Scenario – Getting data from Global Temporary Table

Requirement – Retrieve the ##employee_details table data. The query was as follows –

SELECT * FROM ##employee_details;  

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

Scenario – Deleting Global Temporary table explicitly

Requirement – Delete ##employee_details table from the database. The query was as follows –

 DROP TABLE ##employee_details; 

By executing above query we can delete employee_details Global Temporary Table explicitly.

Scenario – Verifying Global Temporary table deleted or not

Requirement – Verify ##employee_details table deleted from the database 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.