How to Create a Temporary Table in SQL Server

Here are two approaches to create a temporary table in SQL Server:

(1) The SELECT INTO approach:

SELECT Column1,Column2,Column3,...  
INTO #NameOfTempTable
FROM [DatabaseName].[TableSchema].[TableName]
WHERE condition

(2) The CREATE TABLE approach:

CREATE TABLE #NameOfTempTable (
	Column1 datatype,
	Column2 datatype,
	Column3 datatype,
        .
        .
        
	ColumnN datatype
)

Note that once you created the table under the second approach, you’ll need to insert the records into the table using an INSERT INTO query:

INSERT INTO #NameOfTempTable (Column1,Column2,Column3,...) 
SELECT Column1,Column2,Column3,...
FROM [DatabaseName].[TableSchema].[TableName]
WHERE condition

Under both approaches, you must include the hash symbol (#) before the temporary table name.

You can drop a temporary table using the DROP TABLE query (or simply by closing the connection that was used to create the temporary table):

DROP TABLE #NameOfTempTable

In the next section, you’ll see how to create a temporary table in SQL Server using the two approaches described above. You’ll also learn how to create a global temporary table.

(1) Create a Temporary Table in SQL Server Using the SELECT INTO Approach

In the example below, you’ll see how to create a temporary table from an existing table called ‘People‘ (with a dbo schema).

The current ‘People’ table contains the following columns and data:

The database that will be used for this example is called TestDatabase.

The ultimate goal is to create a temporary table (from the ‘People’ table) for all the records where the age is greater than 30.

You can then create a temporary table (called #PeopleTempTable) using the SELECT INTO Approach:

SELECT FirstName,LastName,City,Age 
INTO #PeopleTempTable
FROM [TestDatabase].[dbo].[People]
WHERE Age > 30

After running the query, you’ll notice that 3 rows were affected:

You can check the content of the temporary table by running the following SELECT query:

SELECT * FROM #PeopleTempTable

As you can see, there are currently 3 rows in the table where the age is greater than 30:

You can find the temporary table under the ‘Object Explorer’ by navigating to Databases > System Databases > tempdb > Temporary Tables:

You can drop the temporary table using the DROP TABLE query:

DROP TABLE #PeopleTempTable

After dropping the table, try to run the SELECT query again:

SELECT * FROM #PeopleTempTable

Notice that the table no longer exists:

(2) Create a Temporary Table in SQL Server Using the CREATE TABLE Approach

Let’s recreate the temporary table using the second CREATE TABLE approach:

CREATE TABLE #PeopleTempTable (
	FirstName nvarchar(50),
	LastName nvarchar(50),
	City nvarchar(50),
	Age int
)

Once you created the temporary table, you’ll need to insert the records into the table using the INSERT INTO query:

INSERT INTO #PeopleTempTable (FirstName,LastName,City,Age) 
SELECT FirstName,LastName,City,Age
FROM [TestDatabase].[dbo].[People]
WHERE Age > 30

You’ll notice that 3 records were affected:

Rerun the SELECT query in order to check the content of the table:

SELECT * FROM #PeopleTempTable

As you can see, there are 3 records where the age is greater than 30:

To drop the table use:

DROP TABLE #PeopleTempTable

Create a Global Temporary Table in SQL Server

You can also create a global temporary table by placing double hash (##) before the temporary table name.

The global temporary table will be available across different connections.

Here is the query to create the global temporary table using the SELECT INTO approach:

SELECT FirstName,LastName,City,Age 
INTO ##PeopleTempTable
FROM [TestDatabase].[dbo].[People]
WHERE Age > 30

3 records will be affected:

You can then run the following SELECT query:

SELECT * FROM ##PeopleTempTable

You’ll get the same 3 records where the Age is greater than 30:

To drop the table use:

DROP TABLE ##PeopleTempTable

Optionally, you can use the CREATE TABLE approach in order to create the global temporary table:

CREATE TABLE ##PeopleTempTable (
	FirstName nvarchar(50),
	LastName nvarchar(50),
	City nvarchar(50),
	Age int
)

Then, run the INSERT INTO query to insert the records into the table:

INSERT INTO ##PeopleTempTable (FirstName,LastName,City,Age) 
SELECT FirstName,LastName,City,Age
FROM [TestDatabase].[dbo].[People]
WHERE Age > 30

3 records will be inserted into the table.

Rerun the SELECT query:

SELECT * FROM  ##PeopleTempTable

As before, you’ll see the 3 records where the age is greater than 30: