Temp Table in SQL Server

The temporary or temp table in SQL Server can be created at the run-time and perform all the operations that regular ones can do. There are two types of Temporary Tables in SQL Server, and they are Local and Global temp. Let us see how to work with both Local and Global Temp tables.

The following list shows you where we can use the SQL Server local temp and global temporary tables:

• When we are working with the complex joins, store the temporary data.
• They are useful to replace costly cursors. We can use them to store the result set data and manipulate the data from them.
• We can use these temporary tables when doing a large number of row manipulations in stored procedures. Remember, If we create this one inside a stored procedure for intermediate results, it only applies to that Stored procedure. It means you can not call them outside the stored procedure.

Create Local Temporary or temp Table in SQL Server

The name of the SQL Local temporary temp table starts with the hash (“#”) symbol and is stored in the tempdb. The Local temporary tables are available only in the current connection. If the user disconnects from current instances or closes the query window, it deletes automatically.

SQL Server Local Temp Table Syntax

The syntax behind the local temporary tables

CREATE TABLE #[LocalName]
(
    Column_Name1 Data_Type(Size) [NULL | NOT NULL],
    Column_Name2 Data_Type(Size) [NULL | NOT NULL],
     …
    Column_NameN Data_Type(Size) [NULL | NOT NULL]
);

It is a simple create table statement. Here, LocalName should start with #. Remember, Please provide a unique name. If you give the existing name, it will throw an error.

Let us create one called Emp.

CREATE TABLE #Emp
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL
)

We created a temporary tab and declared 6 Columns. Here, Our first column is the ID of the Integer data type, which won’t allow NULL values. We also defined this column as Identity started with 1 and incremented by 1. Please refresh the object explorer to see the Newly created Local Temporary table.

How to Insert Data into Local Temporary or Temp Table in SQL Server?

Let me insert a few random or sample records into the local temporary table that we created inside the tempdb using the INSERT Statement.

INSERT INTO #Emp (
	    [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
VALUES ('Tutorial', 'Gateway', 'Education', 10000, 200)
      ,('Imran', 'Khan', 'Skilled Professional', 15900, 100)
      ,('Doe', 'Lara', 'Management', 15000, 60)
      ,('Ramesh', 'Kumar', 'Professional', 65000, 630)

We successfully inserted four random records into the Local Temp table in SQL Server.

Select Data from Local Temporary Table

Let me use the SELECT Statement to select the records present in the local temp.

SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
FROM #Emp

Until now, you might wonder why I am writing the Create, Insert, and Select statements in one query window. Because sql local temp tables will last up to a single session, and if you try to call this one from a new query window, it will throw an error. For example, let me call the #Emp from a new query window.

From the below screenshot, you can see that it does not contain our #Emp.

As you can see, the Local Temporary table throws an error stating the Invalid Object name #LocalTemp. Let me close all the existing query windows and refresh the tempdb from Object Explorer.

Create Global Temp or Temporary Table in SQL Server

The name of it starts with the double hash (“##”) symbol and is stored in the tempdb. These are like permanent ones and are available to all users in that instance. The SQL global temp tables will automatically delete if all users disconnect from their session.

Global Temp Table Syntax

The syntax behind the Global temp temporary tables in the SQL Server management studio.

CREATE TABLE ##[Global Temp Name]
(
    Column_Name1 Data_Type(Size) [NULL | NOT NULL],
    Column_Name2 Data_Type(Size) [NULL | NOT NULL],
     …
    Column_NameN Data_Type(Size) [NULL | NOT NULL]
);

The global temp table name should start with ##. Please provide a unique one; otherwise, it throws an error. Let us create a global temporary table called Cust

CREATE TABLE ##Cust
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL
)

We declared 7 Columns for this. See the Newly created Global Temp table.

Insert Data into Global Temporary

Let me insert a few samples or random records into the global temp temporary table in SQL Server that we created inside the tempdb using the INSERT Statement.

INSERT INTO ##Cust (
	    [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales])
VALUES ('Tutorial', 'Gateway', 'Masters Degree', 'Teaching', 12000, 200)
      ,('Imran', 'Khan', 'Bachelors', 'Skilled Professional', 13900, 100)
      ,('Doe', 'Lara', 'Degree', 'Management', 25000, 60)
      ,('Ramesh', 'Kumar', 'Bachelors', 'Professional', 35400, 630)

Select from Global Temp Table in SQL Server

Here, we successfully inserted 4 random records into the ##Cust. So, let me use the SELECT Statement to select the records present in that global temp table in SQL Server.

SELECT [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales] 
FROM ##Cust

Next, let me call the ##Cust Global from a new query window

It displays the records, rather than throwing an error. Now, let me close all the existing query windows and refresh the tempdb from Object Explorer.

Now you can see that there are none in the tempdb database. Please refer to the cursors and stored procedures articles.