SQL Server Local & Global Temp tables

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

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

Create Local Temporary Table in SQL Server

The name of the SQL Local temporary table starts with the hash (“#”) symbol and 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, then it deletes automatically.

SQL Server Local Temp Table Syntax

The syntax behind the local temporary tables in Sql Server

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 gave 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 ID of Integer data type, and it won’t allow NULL values. We also defined this SQL Server column as Identity starts with 1 and incremented by 1. Please refresh the object explorer to see the Newly created Local Temporary table in SQL Server

Insert Data into Local Temporary 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 4 random records into the Local Temp table in SQL Server.

Insert Data Into Local Temporary Table 2

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, then 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 in Sql Server is throwing an error stating that, Invalid Object name #LocalTemp. Now, let me close all the existing query windows, and refresh the tempdb from Object Explorer

Create Global Temporary Table in SQL Server

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

Global Temp Table Syntax

The syntax behind the Global temporary tables in 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 SQL global temp table name should start with ##. Please provide a unique 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 in Sql Server.

Insert Data into Global Temporary

Let me insert a few samples or random records into the global 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. 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

Insert Rows into Global Temporary Table 6

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

It is displaying 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 tempdb database. Please refer to cursors and stored procedures articles.