CREATE TABLE using SELECT INTO in MS SQL Server

Microsoft SQL Server provides a nice feature of creating a table on the fly along with the data from another table using “Select into” statement.

Syntax

1
2
3
4
SELECT columns 
INTO newTableName 
FROM existingTableName 
WHERE conditions;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

columns – Columns to be retrieved from the existing table. 

newTableName – Name of the new table to be created. 

existingTableName – Source table with data. 

conditions – Conditions to filter the data from the existing table. But, this is not a mandatory syntax.

It is important to note that when executing the SELECT INTO statement, the new table will be created without the keys and constraints. It will copy only the columns from the existing table. 

Creating with simple SELECT

1
2
3
SELECT countryName, countryCode 
INTO tblTeam  
FROM tblCountry;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Executing the above query creates the table ‘tblTeam’ with the columns (countryName and countryCode) from the table ‘tblCountry’ and also populates all the records.

To rename the column names in the Team table, use a column alias in the select clause. 

1
2
3
SELECT countryName AS Team --Rename the column 'countryName' to 'Team' 
INTO tblTeam  
FROM tblCountry;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

New column can also be created like NULL AS teamCode or ” AS teamCode

1
2
3
4
SELECT countryName AS Team, 
NULL AS teamCode --'teamCode' Column is created with NULL values
INTO tblTeam 
FROM tblCountry
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Creating with a filter condition

1
2
3
4
SELECT countryName AS Team, countryCode AS teamCode 
INTO tblTeam  
FROM tblCountry 
WHERE ID < 10  --Copies the records with ID < 10;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Using the WHERE condition, we can filter and populate the data from the country table into the team table. The above query populates records with the ID less than 10.

1
2
3
4
SELECT countryName AS Team, countryCode AS teamCode 
INTO tblTeam  
FROM tblCountry 
WHERE 1 = 2  --False condition. Copies only the structure;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

As the where condition returns false in the above query, data will not be populated into the team table. It copies only the table structure. 

Also, check “CREATE TABLE types in MS SQL Server“

Reference – Microsoft SQL Server Select into