What Is Temporary Tables In SQL

Hello Friends, In this article I am going to give a quick overview of temporary tables in SQL Server. Temporary tables are stored in tempdb. They work like a regular table in that you can perform the operations select, insert, update and delete as for a regular table. If created within a hold on procedure they’re destroyed upon completion of the SQL hold on procedure. therefore let’s have a glance at a sensible example of the way to produce temporary tables in SQL Server .The example is developed in SQL Server  using SQL Server Management Studio. There are two types of temporary tables as following type.

(i) Local Temporary Table

(ii) Global Temporary Table

Local temporary tables are the tables keep in tempdb. native temporary tables are temporary tables that are accessible solely to the session that created them. These tables are mechanically destroyed at the termination of the procedure or session. they’re such with the prefix #, as an example #table_name and these worker tables is created with identical name in multiple windows.

Step 1:- Creating Local Temporary Table

Syntax

create table #table_name  
(  
column_name varchar(20),  
column_no int  
)

Example

(1) To Create Temporary Table:

CREATE TABLE #EmpDetails (id INT, name VARCHAR(25))

(2) To Insert Values Into Temporary Table:

INSERT INTO #EmpDetails VALUES (01, 'Dipak'), (02, 'Sagar')

(3) To Select Values from Temporary Table:

SELECT * FROM #EmpDetails

 Result:

Global Temporary Tables

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions & all users. They are dropped automatically when the a last session using the temporary tables has completed. They are specified with the a prefix #, for examples ##table_name.

Step 1:- Creating Global Temporary Table , add the “##” symbol before the table name.

Syntax

create table ##GlobalTemporaryTable  
(  
column_name varchar(20),  
column_no int  
)

Example

(1) To Create Global Table:

CREATE TABLE ##EmpDetails (id INT, name VARCHAR(25))

(2) To Insert Values Into Global Table:

INSERT INTO ##EmpDetails VALUES (01, 'Dipak'), (02, 'Sagar')

(3) To Select Values from Global Table:

SELECT * FROM #EmpDetails

 Result:

Please give your valuable feedback and if you have any questions or issues about this article, please let me know.

Also Check How To Create SSRS Report Using Stored Procedure