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.

idnamegendersalarydepartment
1David JacksonMale5000IT
2Jim JamesonFemale6000HR
3Kate JohnsonFemale7500IT
4Will RayMale6500Marketing
5Shane MathewsFemale5500Finance
6Shed PriceMale8000Marketing
7Viktor SmithMale7200HR
8Vincent SmithsonFemale6600IT
9Janice StreepFemale5400Marketing
10Laura WellsFemale6300Finance
11Mac BullMale5700Marketing
12Patrick PattersonMale7000HR
13Julie OrbisonFemale7100IT
14Elice HemingwayFemale6800Marketing
15Wayne JohnsonMale5000Finance
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.

idnamegendersalarydepartment
1David JacksonMale5000IT
2Jim JamesonFemale6000HR
3Kate JohnsonFemale7500IT
4Will RayMale6500Marketing
5Shane MathewsFemale5500Finance
6Shed PriceMale8000Marketing
7Viktor SmithMale7200HR
8Vincent SmithsonFemale6600IT
9Janice StreepFemale5400Marketing
10Laura WellsFemale6300Finance
11Mac BullMale5700Marketing
12Patrick PattersonMale7000HR
13Julie OrbisonFemale7100IT
14Elice HemingwayFemale6800Marketing
15Wayne JohnsonMale5000Finance

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.

idnamegenderdepartment
1David JacksonMaleIT
2Jim JamesonFemaleHR
3Kate JohnsonFemaleIT
4Will RayMaleMarketing
5Shane MathewsFemaleFinance
6Shed PriceMaleMarketing
7Viktor SmithMaleHR
8Vincent SmithsonFemaleIT
9Janice StreepFemaleMarketing
10Laura WellsFemaleFinance
11Mac BullMaleMarketing
12Patrick PattersonMaleHR
13Julie OrbisonFemaleIT
14Elice HemingwayFemaleMarketing
15Wayne JohnsonMaleFinance

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.

idnamegendersalary
2Jim JamesonFemale6000
3Kate JohnsonFemale7500
5Shane MathewsFemale5500
8Vincent SmithsonFemale6600
9Janice StreepFemale5400
10Laura WellsFemale6300
13Julie OrbisonFemale7100
14Elice HemingwayFemale6800

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.

idnamegendersalarydepartment
1David JacksonMale5000IT
2Jim JamesonFemale6000HR
3Kate JohnsonFemale7500IT
4Will RayMale6500Marketing
5Shane MathewsFemale5500Finance
6Shed PriceMale8000Marketing
7Viktor SmithMale7200HR
8Vincent SmithsonFemale6600IT
9Janice StreepFemale5400Marketing
10Laura WellsFemale6300Finance
11Mac BullMale5700Marketing
12Patrick PattersonMale7000HR
13Julie OrbisonFemale7100IT
14Elice HemingwayFemale6800Marketing
15Wayne JohnsonMale5000Finance

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.

idnamegendersalarydepartment

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.

SQL Server SELECT INTO
Figure: just_the _table Design
SQL Server SELECT INTO
Figure: 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_idcategory_name
1Mobile
2Headphone
3Tablet
4Laptop
5Notebook
6Phablet
Table – categories
product_idcategory_idproduct_namerelease_date
10272Bose Noise Cancelling Headphone 7005/13/2019
10282Sennheiser HD 450BT2/4/2020
10292Sony WH-1000XM38/15/2018
10302SoundMagic ES181/1/2017
10211Apple iPhone 11 Pro9/20/2019
10221Samsung Galaxy Note 10 Plus8/23/2019
10353Samsung Galaxy Tab S610/11/2019
10363Microsoft Surface Pro6/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_namecategory_name
Bose Noise Cancelling Headphone 700Headphone
Sennheiser HD 450BTHeadphone
Sony WH-1000XM3Headphone
SoundMagic ES18Headphone
Apple iPhone 11 ProMobile
Samsung Galaxy Note 10 PlusMobile
Samsung Galaxy Tab S6Tablet
Microsoft Surface ProTablet
iPad AirTablet
Lenovo Tab M8Tablet
Dell Venue 7Tablet
HP 7 VoiceTabTablet
Samsung Galaxy Note 20NULL
Microsoft Universal Foldable KeyboardNULL

Advertisement