SQL SELECT INTO Examples for New Tables - Part 2


By:   |   Updated: 2021-10-06   |   Comments (1)   |   Related: More > TSQL


Master Your Data Environment With DataOps

Free MSSQLTips Webinar: Master Your Data Environment With DataOps

Learn tips and tricks on how to master your data environment with SolarWinds® DataOps solutions, which are designed to help you streamline essential database tasks, database projects, and data-centric application development. Let us show you how to save time on tedious but critical database documentation tasks.


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:

query results

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:

query results

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:

error message

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:

column 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:

column properties

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:

query results

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:

query results

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:






get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips


Article Last Updated: 2021-10-06

Comments For This Article




Monday, October 11, 2021 - 6:05:43 PM - Bruce Hart Back To Top (89324)
Nice article Sergey. I'd like to point out though that SELECT INTO a table creates a schema lock for the entire duration of table build. This could be many minutes if the table is large or getting data from a linked server or other source. Good practice is to create the table first then INSERT into it. An easy way to create the table is to do a SELECT TOP 0 INTO table1 FROM table2 ( or from a view or SQL query) then INSERT INTO table1 SELECT * FROM table2 .


download














get free sql tips
agree to terms