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: