The objective of this SQL Server tutorial is to teach you how to use the SELECT INTO statement to create a new table and copy data into it from an existing table.
What is SELECT INTO in SQL Server?
The SELECT INTO statement allows us to create a table and populate it with data from an already existing table in a single statement. While copying the data into the new table from the existing old table we can choose to copy the entire data or selected columns from the existing table. It is also possible to create an empty table from an existing table without copying any data from it. When the same is done SELECT INTO just copies the table structure from the old table to the new table. However, it is important to note and keep in mind the SELECT INTO does not replicate any of the constraints of the old table into the new table like PRIMARY KEY, FOREIGN KEY, UNIQUE CONSTRAINT, etc. It just replicates the column names, datatype, and data length. It is also possible to create a new table from more than one table using SELECT INTO.
Operation
A SELECT INTO statement is a standalone SQL statement which cannot be appended into another SQL statement like INSERT, UPDATE, DELETE, SELECT etc. However, it can be combined with a JOIN or UNION while creating a new table using multiple tables. We can select the appropriate columns we want from the tables to create the new table.
Syntax
The basic syntax of SQL Server SELECT INTO statement is as follows.
SELECT column_list
INTO new_table
FROM old_table
WHERE condition;
In this syntax,
column_list – the list of columns which should be created and copied from the old table. We can specify * if we want to create and copy data from all the columns in the old table into the new table.
new_table – the new table which does not exist but will be created using information from the old table.
old_table – the source of table structure and data for the new table.
WHERE condition – Optional. This is used to specify some conditions while selecting data. In case you are not using the WHERE clause, all the rows available will be selected.
SQL Server SELECT INTO Examples
Let us consider some practical examples of how to use the SELECT INTO statement.
Suppose we have a table called employees in the company database containing information about the employees of all departments. The table is represented below. We will use it as the source table for all our examples from which we will create the new tables.
id
name
gender
salary
department
1
David Jackson
Male
5000
IT
2
Jim Jameson
Female
6000
HR
3
Kate Johnson
Female
7500
IT
4
Will Ray
Male
6500
Marketing
5
Shane Mathews
Female
5500
Finance
6
Shed Price
Male
8000
Marketing
7
Viktor Smith
Male
7200
HR
8
Vincent Smithson
Female
6600
IT
9
Janice Streep
Female
5400
Marketing
10
Laura Wells
Female
6300
Finance
11
Mac Bull
Male
5700
Marketing
12
Patrick Patterson
Male
7000
HR
13
Julie Orbison
Female
7100
IT
14
Elice Hemingway
Female
6800
Marketing
15
Wayne Johnson
Male
5000
Finance
Table: Employee
1) SQL Server SELECT-INTO – creating an exact replica
We will start with the simplest example of creating an exact replica of an existing table. It is often done in real-time to take a quick backup of the data of an existing table before making data or structural changes to the table. the following query does the same using the * wild card character.
SELECT *
INTO employee_backup
FROM employee;
We can check whether the new table has been created and data copied into it using below SEELCT query.
SELECT *
FROM
employee_backup;
It will generate the following output from which we can see that the new table is a replica of the old table.
id
name
gender
salary
department
1
David Jackson
Male
5000
IT
2
Jim Jameson
Female
6000
HR
3
Kate Johnson
Female
7500
IT
4
Will Ray
Male
6500
Marketing
5
Shane Mathews
Female
5500
Finance
6
Shed Price
Male
8000
Marketing
7
Viktor Smith
Male
7200
HR
8
Vincent Smithson
Female
6600
IT
9
Janice Streep
Female
5400
Marketing
10
Laura Wells
Female
6300
Finance
11
Mac Bull
Male
5700
Marketing
12
Patrick Patterson
Male
7000
HR
13
Julie Orbison
Female
7100
IT
14
Elice Hemingway
Female
6800
Marketing
15
Wayne Johnson
Male
5000
Finance
2) SQL Server SELECT INTO – recreating selected columns
We will now modify the above query a bit to create a new table using only selected columns from the existing table instead of replicating all the columns. The following query does the same. It creates a new table called employee_backup_2 with only the columns specified in the query.
SELECT
id, name, gender, department
INTO employee_backup_2
FROM employee;
We can check whether the new table has been created and data copied into it using below SEELCT query.
SELECT *
FROM employee_backup_2;
It will generate the following output. From the output we can see that the new table has only the specified 4 columns.
id
name
gender
department
1
David Jackson
Male
IT
2
Jim Jameson
Female
HR
3
Kate Johnson
Female
IT
4
Will Ray
Male
Marketing
5
Shane Mathews
Female
Finance
6
Shed Price
Male
Marketing
7
Viktor Smith
Male
HR
8
Vincent Smithson
Female
IT
9
Janice Streep
Female
Marketing
10
Laura Wells
Female
Finance
11
Mac Bull
Male
Marketing
12
Patrick Patterson
Male
HR
13
Julie Orbison
Female
IT
14
Elice Hemingway
Female
Marketing
15
Wayne Johnson
Male
Finance
3) SQL Server SELECT INTO – with WHERE condition
So far, we have seen examples where we have copied all records from the old to the new table without specifying any condition to select the records. Now we will use the WHERE condition to restrict the records that are copied into the new table. The following query does the same and selects and copies records on the basis of a condition.
SELECT *
INTO female_employees
FROM employee
WHERE gender=’FEMALE’;
We can check whether the new table has been created and appropriate data copied into it using below SELECT query.
SELECT *
FROM employee_backup_2;
It will generate the following output. From the output we can see that the new table consists of the records of female employees only as specified in the WHERE condition.
id
name
gender
salary
2
Jim Jameson
Female
6000
3
Kate Johnson
Female
7500
5
Shane Mathews
Female
5500
8
Vincent Smithson
Female
6600
9
Janice Streep
Female
5400
10
Laura Wells
Female
6300
13
Julie Orbison
Female
7100
14
Elice Hemingway
Female
6800
4) SQL Server SELECT INTO – replicating an existing table into another database
As we all know tables exist in a database. We can use SELECT INTO to duplicate an existing table in the same database or into another database. The database must already exist or must be created so that the duplicate table can be created in it. In this example we first create a database and then use SELECT INTO to replicate the existing table employee in that database. The first query in the text box below creates the database company_db and the second SELECT INTO query creates the table company_employee in the database company_db.
create database company_db;
go;
SELECT *
INTO company_db.dbo.company_employee
FROM employee;
We can check the result of the same with the following SELECT query which shows the new table company_employee. The first query is if you are not connected to the database company_db and so refers to the table by its complete absolute name. the second query first connects the database and then does a select on the table and hence only the table name suffices.
SELECT *
FROM company_db.dbo.company_employee;
OR
Use company_db;
Go;
SELECT * from company_employee;
The queries will list the complete table as below.
id
name
gender
salary
department
1
David Jackson
Male
5000
IT
2
Jim Jameson
Female
6000
HR
3
Kate Johnson
Female
7500
IT
4
Will Ray
Male
6500
Marketing
5
Shane Mathews
Female
5500
Finance
6
Shed Price
Male
8000
Marketing
7
Viktor Smith
Male
7200
HR
8
Vincent Smithson
Female
6600
IT
9
Janice Streep
Female
5400
Marketing
10
Laura Wells
Female
6300
Finance
11
Mac Bull
Male
5700
Marketing
12
Patrick Patterson
Male
7000
HR
13
Julie Orbison
Female
7100
IT
14
Elice Hemingway
Female
6800
Marketing
15
Wayne Johnson
Male
5000
Finance
5) SQL Server SELECT INTO –creating an empty table
In all the above examples we created a replica of the existing table employee either replicating all the columns or select columns. But it is also possible to just replicate the structure of a table without copying any data into the new table. Please keep in mind that only the column structure will be replicated but not any constraints as already mentioned above in the introduction. The following query does the same. the 1=0 in the WHERE condition basically means where true equals false which is a non-satisfiable condition and hence no records are returned from the old table and nothing copied into the new table.
SELECT *
INTO just_the_table
FROM employee
WHERE 1 = 0;
We can check whether the new table has been created or not with the below SELECT query.
SELECT *
FROM just_the_table;
It will generate the following output showing just the table column headers.
id
name
gender
salary
department
Lastly if we check the table design of the new table just_the_table and the old table employee in SQL Server Management Studio (right click the table and select ‘Design’) or using the below command we will see that the new table does not have any PRIMARY KEY while in the old table the first table column ‘id’ is a PRIMARY KEY column as indicated by the key icon next to it.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='just_the_table';
AND
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='employee';
The output is below.
Figure: just_the _table DesignFigure: employee Design
6) SQL Server SELECT INTO –creating a new table from multiple tables
In this last example we will create a new table from 2 existing tables. The 2 tables are shown below. One is a categories table (containing the list of categories of electronic products) and another products table (containing the list of specific products).
category_id
category_name
1
Mobile
2
Headphone
3
Tablet
4
Laptop
5
Notebook
6
Phablet
Table – categories
product_id
category_id
product_name
release_date
1027
2
Bose Noise Cancelling Headphone 700
5/13/2019
1028
2
Sennheiser HD 450BT
2/4/2020
1029
2
Sony WH-1000XM3
8/15/2018
1030
2
SoundMagic ES18
1/1/2017
1021
1
Apple iPhone 11 Pro
9/20/2019
1022
1
Samsung Galaxy Note 10 Plus
8/23/2019
1035
3
Samsung Galaxy Tab S6
10/11/2019
1036
3
Microsoft Surface Pro
6/15/2017
The following query creates a new table called electronics using a LEFT JOIN between the above 2 tables. The LEFT JOIN copies all records from the left table and only matching records from the right table.
SELECT
product_name, category_name
INTO electronics
FROM products
LEFT JOIN categories
ON products.category_id=categories.category_id;
We can check the outcome of the query by running a select on the new table electronics. It will produce the following output.
