SQL SELECT INTO Advanced Topics

Problem

In the previous article, Creating a table using the SELECT INTO clause – Part 1, we learned how to create tables using the SELECT INTO statement. In this tutorial, we will continue exploring several table creation tips via the SELECT INTO statement and discuss more advanced options of this statement.

Solution

First, we will create a test environment with the following syntax:

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

A database with a single table is created. The table has indexes, constraints, and contains sample data:

Creating a table from another table with a computed column with SQL SELECT INTO

In the previous article, we saw that while the SELECT INTO statement transfers identity specification and nullability of a column from the base table to the new one, it does not transfer indexes, other constraints, etc. Now, let’s see what happens if we create a new table from an existing table containing a computed column:

USE TestDB
GO
 
--Adding a computed column
ALTER TABLE TestTable ADD TenPercentofVal AS (Val*10/100)
GO
 
SELECT * INTO TableE FROM TestTable 
GO
 
SELECT * FROM TableE

The result shows that the TenPercentVal column (the computed column of the base table) is not computed in the new – TableE table, but it is just a regular column with the same data type and contains the computed values of the base table’s corresponding column:

Specifying an identity column for the new table

In the previous article, we discussed that it is possible to redefine an identity column for a table in the SELECT INTO statement if we do not want to use the source table’s corresponding column. However, what if we specify a new identity definition in the SELECT INTO statement and include all columns of the base table (including identity) as well?

 USE TestDB
GO
 
SELECT IDENTITY (INT, 100, 10) AS TableFID, tt.* 
INTO TableF 
FROM TestTable tt
GO
 
SELECT * FROM TableF 

As we can see, an error message will appear stating that the new table inherits the base table’s identity property, and the new identity column cannot be added:

Therefore, if we are defining a new identity column in the SELECT INTO statement, we should exclude the base table’s identity column in the selected column list:

USE TestDB
GO
 
SELECT IDENTITY (INT, 100, 10) AS TableFID, tt.Val, tt.TenPercentofVal 
INTO TableF 
FROM TestTable tt
GO
 
SELECT * FROM TableF

In this case, TableF will be successfully created with a new identity column:

Now, let’s create a new table via SELECT INTO clause by joining two tables having identity columns.

USE TestDB
GO
 
SELECT a.ID AS aID, e.ID AS eID, a.Val AS aVal, e.Val AS eVal 
INTO TableG 
FROM TableA a INNER JOIN TableE e ON a.ID=e.ID
GO
 
SELECT * FROM TableG

Despite the fact that we have included identity columns of both tables, the new table does not inherit any identity properties:

Now, let’s exclude TableE’s identity column from the SELECT INTO statement leaving only one identity column:

USE TestDB
GO
 
SELECT a.ID AS aID, a.Val AS aVal, e.Val AS eVal 
INTO TableH 
FROM TableA a INNER JOIN TableE e ON a.ID=e.ID
GO
 
SELECT * FROM TableH

If we look at the result, we can see that even if we include only one table’s identity column in the SELECT INTO statement, the new table does not have an identity column:

Thus, it is clear that if the SELECT statement contains joins, identity properties are not transferred.

Creating temporary tables via the SELECT INTO statement

It is possible to create both local and global temporary tables using the SELECT INTO statement. Let’s try this query:

USE TestDB
GO
 
--Creating a local temp table
SELECT * 
INTO #tmpTestTable
FROM TestTable
GO
 
--Creating a global temp table
SELECT * 
INTO ##tmpTestTable
FROM TestTable
GO
 
SELECT * FROM #tmpTestTable
 
SELECT * FROM ##tmpTestTable

We can see that a global temporary table – ##tmpTestTable and a local temporary table #tmpTestTable are created with the structure and data of the TestTable table:

Nevertheless, it is not possible to create a table variable via the SELECT INTO statement.

Specifying the filegroup in the SELECT INTO statement

By default, a new table specified in the SELECT INTO statement is created in the default filegroup. However, it is possible to specify a different filegroup in this statement. In the example below, we add a new filegroup, file, and schema to the TestDB database. Then, via the SELECT INTO clause we create a copy of TestTable in the new schema and place the table in the new filegroup with the following SQL statement:

USE master
GO
 
ALTER DATABASE TestDB ADD FILEGROUP FG_Test2
 
ALTER DATABASE TestDB
ADD FILE
(
   NAME='TestDB2',
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_2.mdf'
)
TO FILEGROUP FG_Test2
GO
 
USE TestDB
GO
 
CREATE SCHEMA test
GO
 
SELECT * INTO test.TestTable ON FG_Test2 FROM TestTable
 
SELECT * FROM test.TestTable

Hence, we have a copy of TestTable which is located in a different filegroup and schema. In other words, the new table is both logically and physically separated from the base table:

Conclusion

All in all, we have explored various features and limitations of the SELECT INTO clause and seen how it can be useful in creating new tables based on existing ones in a SQL database.

Next Steps

For more information, please use the following links:

• SELECT – INTO Clause (Transact-SQL)
• CREATE TABLE (Transact-SQL)

Sergey Gigoyan

Sergey Gigoyan (LinkedIn) is a Senior Technical Architect specializing in data and databases with more than 15 years of experience.  Sergey focuses on modern data architectures, database design and development, performance tuning and optimization, high availability solutions, BI development and DW design. He has worked with SQL Server, Oracle, and PostgreSQL databases, as well as cloud-based data solutions (AWS and Azure).  Sergey also has extensive experience with modern data stacks such as Snowflake and dbt.

Sergey’s experience spans various industries.  He had the privilege of working with IT giants such as Oracle as a Principal Data Engineer and BlackBerry as well as innovative startups.  He helped deliver complex database solutions and advanced data strategies.

Sergey is also the author of “Building a Successful Career in IT – How I Did It” where he provides actionable advice on thriving in the ever-evolving IT industry.

• MSSQLTips Awards: Champion (100+ tips) – 2024 | Author of the Year – 2020