Select Rows from SQL Local Temp Table 3

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. Let us see how to work with both Local and Global Temp tables. The following list shows you where we can use the 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 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 Table in SQL Server

The name of the SQL Local temporary 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 SQL Server column as Identity started with 1 and incremented by 1. Please refresh the object explorer to see the Newly created Local Temporary table.

Create Local Temp Table in SQL Server 1

Insert Data into Local Temporary Table

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.

Insert Data 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
Select Rows 3

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.

Error Accessing 4

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 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 temporary tables in the 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 otherwise, and 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.

Create Global Temp Table in Sql Server 5

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. 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
Insert Rows 6

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

Select Records

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.

Delete

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