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_idemp_nameemp_hremp_roleemp_typetech_tower
224Annie Bora                   Gaurav SharmaIndividual Contributor       ContractWintel
214Deep Sharma                  Gaurav SharmaIndividual Contributor       ProbationWintel
236Sunita Rai                   Seema ShahManager                      PermanentUnix
221Rajesh Pandey                Manoj NagpalExecutive                    PermanentCybersecurity
113Nitin Goyal                  Seema ShahManager                      ContractCybersecurity
155Calvin Mathews               Seema ShahManager                      PermanentUnix
111Jennifer Joseph              Manoj NagpalExecutive                    PermanentUnix
243Syed Khan                    Seema ShahIndividual Contributor       PermanentWintel
109Sumit Singh                  Manoj NagpalExecutive                    PermanentUnix
110Nupur Seth                   Gaurav SharmaManager                      ContractWintel
215Asifa Ahmed                  Riya NaikIndividual Contributor       ProbationCybersecurity
226Abhijeet Baruah              Riya NaikIndividual Contributor       PermanentCybersecurity
Table Name:  employees

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_idemp_nameemp_hremp_roleemp_typetech_tower
224Annie Bora                   Gaurav SharmaIndividual Contributor       ContractWintel
214Deep Sharma                  Gaurav SharmaIndividual Contributor       ProbationWintel
236Sunita Rai                   Seema ShahManager                      PermanentUnix
221Rajesh Pandey                Manoj NagpalExecutive                    PermanentCybersecurity
113Nitin Goyal                  Seema ShahManager                      ContractCybersecurity
155Calvin Mathews               Seema ShahManager                      PermanentUnix
111Jennifer Joseph              Manoj NagpalExecutive                    PermanentUnix
243Syed Khan                    Seema ShahIndividual Contributor       PermanentWintel
109Sumit Singh                  Manoj NagpalExecutive                    PermanentUnix
110Nupur Seth                   Gaurav SharmaManager                      ContractWintel
215Asifa Ahmed                  Riya NaikIndividual Contributor       ProbationCybersecurity
226Abhijeet Baruah              Riya NaikIndividual Contributor       PermanentCybersecurity
Table Name:  technology_employees

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_idemp_nameemp_hremp_roleemp_typetech_tower
224Annie Bora                   Gaurav SharmaIndividual Contributor       ContractWintel
214Deep Sharma                  Gaurav SharmaIndividual Contributor       ProbationWintel
236Sunita Rai                   Seema ShahManager                      PermanentUnix
221Rajesh Pandey                Manoj NagpalExecutive                    PermanentCybersecurity
113Nitin Goyal                  Seema ShahManager                      ContractCybersecurity
155Calvin Mathews               Seema ShahManager                      PermanentUnix
Table Name:  technology_employees

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_idemp_nameemp_hremp_type
110Nupur Seth                   Gaurav Sharma                Contract           
214Deep Sharma                  Gaurav Sharma                Probation          
224Annie Bora                   Gaurav Sharma                Contract           
243Syed Khan                    Seema Shah                   Permanent          
Table Name:  wintel_employees

Advertisement