SQL Server SELECT INTO

Learning Objective

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

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.

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

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.

product_name	category_name
Bose Noise Cancelling Headphone 700	Headphone
Sennheiser HD 450BT	Headphone
Sony WH-1000XM3	Headphone
SoundMagic ES18	Headphone
Apple iPhone 11 Pro	Mobile
Samsung Galaxy Note 10 Plus	Mobile
Samsung Galaxy Tab S6	Tablet
Microsoft Surface Pro	Tablet
iPad Air	Tablet
Lenovo Tab M8	Tablet
Dell Venue 7	Tablet
HP 7 VoiceTab	Tablet
Samsung Galaxy Note 20	NULL
Microsoft Universal Foldable Keyboard	NULL