SQL SELECT INTO to a Create a Table

Problem

There are several ways of creating tables in the SQL Server. Using the CREATE TABLE T-SQL statement and SQL Server Management Studio (SSMS) Object Explorer are two such examples, and perhaps, the most popular ones. Besides the aforementioned ways, it is also possible to create a table using the SQL SELECT INTO statement. In this article, we will explore several techniques for creating tables using this statement.

Solution

We will use simple examples with the SELECT INTO clause to illustrate table creation in this tutorial.

Create Sample SQL Database with SQL Data

Before moving forward, let’s create a test environment using the following syntax below:

USE master
GO
 
CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
CREATE TABLE TestTable
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   Val INT NOT NULL,
   CHECK(Val > 0)
)
GO
 
CREATE UNIQUE INDEX UIX_TestTable_Val ON TestTable(Val)
GO
 
INSERT INTO TestTable(Val) 
VALUES(10),(20),(30),(40),(50)
GO
 
SELECT * FROM TestTable

As we can see, the code creates a database, a table within this database with constraints, indexes, etc. and inserts sample data into it:

Creating a table from another table using SELECT INTO

With the SELECT INTO statement, we can create a new table based on another table. The following code creates a new table – TableA, and inserts all rows from the source table into it:

USE TestDB
GO
 
SELECT * INTO TableA FROM TestTable
GO 
 
SELECT * FROM TableA

The last SELECT statement in the code retrieves all columns and rows of the new table. So, we can see that it has the same columns and data in the result set:

At first glance, TableA and TestTable seem identical, but when we compare their structures in a more detailed way, we will find several differences. In the image below from SSMS table properties we can see what is different:

After carefully looking at their structures, we can conclude that neither indexes, primary key or check constraints of TestTable are transferred to TableA.

We can see that the column types, NOT NULL constraint, and identity specification are transferred to the new table.

Creating a table from another table using SELECT INTO and copying filtered data

Sometimes it is needed to copy only some of the data from the source into the target table. In that case, we can use a WHERE condition to filter the data transferred:

USE TestDB
GO
 
SELECT Val INTO TableB FROM TestTable WHERE ID > 3
GO
 
SELECT * FROM TableB

The T-SQL code above created a new table – TableB, based on TestTable but copies only data that meets the search condition:

Creating an empty table using SELECT INTO based on another table

What if we just need an empty copy of a specific table? In that case, we can use a condition in the WHERE clause that is always false, such as this:

USE TestDB
GO
 
SELECT * INTO TableC FROM TestTable WHERE 0 > 1
GO
 
SELECT * FROM TableC

As 0 is always less than 1, the condition in the WHERE clause is always false. As a result, an empty table – TableC is created from TestTable. Like in the previous examples, the column names, identity specification, and the nullability of the columns are inherited from the base table:

It is also possible to achieve the same result by using another statement. The statement below uses a bit different approach to create an empty table from TestTable:

USE TestDB
GO
 
SELECT TOP 0 * INTO TableD FROM TestTable 
GO
 
SELECT * FROM TableD

As the query result shows, selecting top 0 rows from the base table in the SELECT INTO statement results in the creation of an empty table with the same structure:

Some other ways of creating a table via SELECT INTO clause

It is also possible to create a new table by copying only some selected columns of the source table:

USE TestDB
GO
 
SELECT Val INTO TableX FROM TestTable 
GO
 
SELECT * FROM TableX

The code above creates a new table with one column selected from the base table:

The following code creates a new table using only the Val column from the base table, specifies a new identity column, and defines a new column with a constant value:

USE TestDB
GO
 
SELECT IDENTITY (INT, 1, 1) AS ID, 1 AS IsUsed, Val INTO TableY FROM TestTable 
GO
 
SELECT * FROM TableY

Thus, the new table contains the data from the base table in the Val column, newly generated identity values, and a constant value – 1 in the IsUsed column:

Finally, it is worth mentioning that we can create a table even without using a base table. For example, the code below creates a table via SELECT INTO by just selecting a number:

USE TestDB
GO
 
SELECT 1 AS Val INTO TableZ 
GO
 
SELECT * FROM TableZ

So, a one-column, one-row table is created:

Conclusion

To sum up, we have learned how to create a new table based on an existing table using the SELECT INTO clause. In the next article, we will discuss more advanced details of table creation via the aforesaid statement.

Next Steps

To read additional information related to this topic please follow the links below:

Resources

Community

Subscribe