SQL SELECT INTO Examples for New Tables - Part 2
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.
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:
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.
For more information, please use the following links:
About the author
View all my tips
Article Last Updated: 2021-10-06