In SQL Server you can create a temporary table based on another table by using the SELECT... INTO syntax.

You can create the table with or without data. In other words, you can copy data from the original table if you wish, or you can create the table without any data.

Example 1 – Create Table With Data

Here’s an example of creating a temporary table based on a persistent table, and copying all data in the process.

USE Test;
SELECT 
  CatId,
  CatName,
  Phone
INTO #TempCatsAllData
FROM dbo.Cats;

Let’s view the result.

SELECT * FROM #TempCatsAllData;

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 1       | Garfield  | 9871237654 |
| 2       | Felix     | 8871237651 |
| 3       | Tom       | 7871237652 |
| 4       | Fetch     | 6871237653 |
+---------+-----------+------------+

You can also filter the data with a WHERE clause if you need to.

Example 2 – Create Table Without Data

Here’s an example of creating a table without data.

USE Test;
SELECT 
  CatId,
  CatName,
  Phone
INTO #TempCatsNoData
FROM dbo.Cats
WHERE 1 = 0;

In this example, I use WHERE 1 = 0 to return no data.

Example 3 – Check the Tables

In this example I compare the two temporary tables with the original table. I do this by querying the sys.columns catalog view in the original database (for the original table) and in the tempdb database (for the temporary tables).

USE Test;
SELECT
  name AS [Column Name],
  TYPE_NAME(user_type_id) AS [Data Type],
  max_length,
  [precision],
  scale,
  is_nullable
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'Cats';

USE tempdb;
SELECT
  name AS [Column Name],
  TYPE_NAME(user_type_id) AS [Data Type],
  max_length,
  [precision],
  scale,
  is_nullable
FROM sys.columns
WHERE OBJECT_NAME(object_id) LIKE '#TempCatsAllData%';

SELECT
  name AS [Column Name],
  TYPE_NAME(user_type_id) AS [Data Type],
  max_length,
  [precision],
  scale,
  is_nullable
FROM sys.columns
WHERE OBJECT_NAME(object_id) LIKE '#TempCatsNoData%';

Result:

Changed database context to 'Test'.
+---------------+-------------+--------------+-------------+---------+---------------+
| Column Name   | Data Type   | max_length   | precision   | scale   | is_nullable   |
|---------------+-------------+--------------+-------------+---------+---------------|
| CatId         | int         | 4            | 10          | 0       | 0             |
| CatName       | varchar     | 70           | 0           | 0       | 1             |
| Phone         | varchar     | 10           | 0           | 0       | 1             |
+---------------+-------------+--------------+-------------+---------+---------------+
(3 rows affected)
Changed database context to 'tempdb'.
+---------------+-------------+--------------+-------------+---------+---------------+
| Column Name   | Data Type   | max_length   | precision   | scale   | is_nullable   |
|---------------+-------------+--------------+-------------+---------+---------------|
| CatId         | int         | 4            | 10          | 0       | 0             |
| CatName       | varchar     | 70           | 0           | 0       | 1             |
| Phone         | varchar     | 10           | 0           | 0       | 1             |
+---------------+-------------+--------------+-------------+---------+---------------+
(3 rows affected)
+---------------+-------------+--------------+-------------+---------+---------------+
| Column Name   | Data Type   | max_length   | precision   | scale   | is_nullable   |
|---------------+-------------+--------------+-------------+---------+---------------|
| CatId         | int         | 4            | 10          | 0       | 0             |
| CatName       | varchar     | 70           | 0           | 0       | 1             |
| Phone         | varchar     | 10           | 0           | 0       | 1             |
+---------------+-------------+--------------+-------------+---------+---------------+
(3 rows affected)