SQL Server INSERT INTO SELECT

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the INSERT statement to enter one or more records (i.e. rows of information) into a new or existing table from another table.

What is INSERT in SQL Server?

INSERT is the most basic of all SQL queries. It is the first query to be executed post table creation for entering records into a table. Every other DML query becomes applicable and follows the INSERT query. It is important to note and keep in mind that INSERT enters values in all the columns in a table row. It cannot be used to enter data in some columns in a row leaving out the other columns. Selective insertion of data is not possible.

Operation

In its simple form, the INSERT statement is used to enter one or more records into a table by explicitly specifying the values for the different columns. However here we will see how we can insert one or more records into a table from another table which already contains the required data. The important point to keep in mind here is that the other table (i.e. source table) should contain the entire data or more data than is required by the table into which data is being inserted (i.e. target table). That is the source table must be either a superset or contain the same data set as the target table.

SQL Server INSERT INTO SELECT Syntax

The basic syntax of SQL Server INSERT statement to enter one or more records into a table from another table uses the SELECT clause to fetch the data from the source table and is as below.

INSERT [TOP (value) | (value) PERCENT] INTO target_table
[column_list]
SELECT [column_list] FROM source_table <- SELECT SUBQUERY
WHERE [condition];

In this syntax,

• INSERT – clause used to insert one or more records into a new or existing table.
• TOP – optional. Used to specify the number or records or percentage of records to be copied from the source table.
• value – non-negative integer value for the number or percentage of records to be copied from the source table.
• PERCENT – keyword used to indicate that the value specified is a percentage value.
• INTO – keyword used with INSERT to specify the table into which data should be entered.
• target_table – the table into which data is being copied.
• column list – the list of all columns or fields in the table which need to be populated with data.
• source_table – the table from which data is being copied.
• WHERE conditions –   Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.

SQL Server INSERT INTO SELECT Examples

• Columns are also referred to as fields or attributes and the terms are used interchangeably.
• A row of information in a table is called a tuple.

Suppose we have a table called ‘employees’ containing the following information about the company’s employees. We will use this as the source table to copy data from and explore the different INSERT INTO SELECT usage scenarios.

emp_id	emp_name	emp_hr	emp_role	emp_type	tech_tower
224	Annie Bora	Gaurav Sharma	Individual Contributor	Contract	Wintel
214	Deep Sharma	Gaurav Sharma	Individual Contributor	Probation	Wintel
236	Sunita Rai	Seema Shah	Manager	Permanent	Unix
221	Rajesh Pandey	Manoj Nagpal	Executive	Permanent	Cybersecurity
113	Nitin Goyal	Seema Shah	Manager	Contract	Cybersecurity
155	Calvin Mathews	Seema Shah	Manager	Permanent	Unix
111	Jennifer Joseph	Manoj Nagpal	Executive	Permanent	Unix
243	Syed Khan	Seema Shah	Individual Contributor	Permanent	Wintel
109	Sumit Singh	Manoj Nagpal	Executive	Permanent	Unix
110	Nupur Seth	Gaurav Sharma	Manager	Contract	Wintel
215	Asifa Ahmed	Riya Naik	Individual Contributor	Probation	Cybersecurity
226	Abhijeet Baruah	Riya Naik	Individual Contributor	Permanent	Cybersecurity

1)  SQL Server INSERT INTO SELECT – without constraint example

First, we will create the following table containing the exact same columns as the employees table and call it the technology_employees table.

CREATE TABLE technology_employees (
emp_id TINYINT PRIMARY KEY
,emp_name VARCHAR(50)
,emp_hr VARCHAR(50)
,emp_role CHAR(20)
,emp_type CHAR(20)
,tech_tower CHAR(20));

Now we can insert records into this table from the source table using any one of the following queries both of which will copy all records from the source table to the target table. The first query uses the * wild card character to select and copy all records from the source to target table. The second query explicitly specifies the columns to copy from the source table. Keep in mind that the column list should not be in bracket in the SELECT subquery. Otherwise the query will not run and throw an error.

INSERT INTO technology_employees(
emp_id
,emp_name
,emp_hr
,emp_role
,emp_type
,tech_tower
) 
SELECT * 
FROM employees;

INSERT INTO technology_employees 
(emp_id, emp_name, emp_hr, emp_role, emp_type, tech_tower) 
SELECT emp_id, emp_name, emp_hr, emp_role, emp_type, tech_tower 
FROM employees;

After running any one of the above queries we can run a select query and it will show the following output with all records copied from the source to target table.

emp_id	emp_name	emp_hr	emp_role	emp_type	tech_tower
224	Annie Bora	Gaurav Sharma	Individual Contributor	Contract	Wintel
214	Deep Sharma	Gaurav Sharma	Individual Contributor	Probation	Wintel
236	Sunita Rai	Seema Shah	Manager	Permanent	Unix
221	Rajesh Pandey	Manoj Nagpal	Executive	Permanent	Cybersecurity
113	Nitin Goyal	Seema Shah	Manager	Contract	Cybersecurity
155	Calvin Mathews	Seema Shah	Manager	Permanent	Unix
111	Jennifer Joseph	Manoj Nagpal	Executive	Permanent	Unix
243	Syed Khan	Seema Shah	Individual Contributor	Permanent	Wintel
109	Sumit Singh	Manoj Nagpal	Executive	Permanent	Unix
110	Nupur Seth	Gaurav Sharma	Manager	Contract	Wintel
215	Asifa Ahmed	Riya Naik	Individual Contributor	Probation	Cybersecurity
226	Abhijeet Baruah	Riya Naik	Individual Contributor	Permanent	Cybersecurity

2)  SQL Server INSERT INTO SELECT – with TOP example

Now we will see how we can insert a select number of records instead of copying all records from one table to another. This can be done with the TOP clause which allows us to specify a non-negative integer value for the number of records to be inserted or a non-negative percentage value for what percentage of records should be copied from the source table. Before doing so we will wipe out the already inserted data from the technology_employees table using the truncate command.

TRUNCATE TABLE technology_employees;

Now we will insert 6 records into the table with the help of TOP using the following queries. Both the queries will achieve the same result. The difference is in the mode of instruction – the first query specifies the number 6 directly and the second query specifies that 50 % of records should be copied from the source table (which in number terms is the same i.e. 50% of 12 records = 6 records).

INSERT TOP (6) INTO technology_employees 
SELECT * from employees;   
OR
INSERT TOP (6) INTO technology_employees 
(emp_id, emp_name, emp_hr, emp_role, emp_type, tech_tower) 
SELECT emp_id, emp_name, emp_hr, emp_role, emp_type, tech_tower 
FROM employees;

INSERT TOP (50) PERCENT INTO technology_employees 
SELECT * from employees;   
OR   
INSERT TOP (50) PERCENT INTO technology_employees 
(emp_id, emp_name, emp_hr, emp_role, emp_type, tech_tower) 
SELECT emp_id, emp_name, emp_hr, emp_role, emp_type, tech_tower 
FROM employees;

The result can be checked by running a select query which will generate the below output showing the 6 copied records.

emp_id	emp_name	emp_hr	emp_role	emp_type	tech_tower
224	Annie Bora	Gaurav Sharma	Individual Contributor	Contract	Wintel
214	Deep Sharma	Gaurav Sharma	Individual Contributor	Probation	Wintel
236	Sunita Rai	Seema Shah	Manager	Permanent	Unix
221	Rajesh Pandey	Manoj Nagpal	Executive	Permanent	Cybersecurity
113	Nitin Goyal	Seema Shah	Manager	Contract	Cybersecurity
155	Calvin Mathews	Seema Shah	Manager	Permanent	Unix

3)  SQL Server INSERT INTO SELECT –with WHERE condition example

CREATE TABLE wintel_employees (
emp_id TINYINT PRIMARY KEY
,emp_name VARCHAR(50)
,emp_hr VARCHAR(50)
,emp_type CHAR(20)
);  

To check the usage of the WHERE condition in an INSERT INTO SELECT subquery we will create a table called wintel_employees with the following columns.

Now we will copy records of all employees belonging to the Wintel tower into the new table with the help of WHERE condition using the following query. It is important to note that we cannot use the * wild card here in the SELECT subquery. This is because the number of columns should be the same in the INSERT and SELECT list. If we use * here with the SELECT subquery the number of columns in the SELECT list will more since the employees table has 6 columns and the wintel_employees only 4 columns which will make the query fail.

INSERT INTO wintel_employees 
(emp_id, emp_name, emp_hr, emp_type) 
SELECT emp_id, emp_name, emp_hr, emp_type 
FROM employees 
WHERE tech_tower='Wintel';

After insertion we can check the result by running a SELECT query. It will produce the following output from which we can see that all the records that have been copied are of employees belonging to the Wintel tower.

emp_id	emp_name	emp_hr	emp_type
110	Nupur Seth	Gaurav Sharma	Contract
214	Deep Sharma	Gaurav Sharma	Probation
224	Annie Bora	Gaurav Sharma	Contract
243	Syed Khan	Seema Shah	Permanent