Summary -

In this topic, we described about the INSERT INTO SELECT with detailed example.

INSERT INTO SELECT statement used for bulk rows insertions. During the bulk insert, we can copy rows from one table to another table of same structure, source and destination columns have same data type, but the column name may be different.

Syntax -

INSERT INTO table_name1
 (column1, column2, …, columnN)
 SELECT column1, column2, …, columnN 
 FROM table_name2 [WHERE condition]; 
  • column1, column2, …, columnN - Specifies the column names from table_name1, table_name2.
  • table_name1, table_name2 – Specifies the name of the tables.

Example-

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Insert all column values from existing table to another new table.

Requirement - Fetching all details of existing employee_details table to another new employee_info table. The query was as follows –

INSERT INTO employee_info SELECT emp_id,emp_name,designation,
manager_id,date_of_hire, salary, dept_id FROM employee_details; 

The same query can be written as –

INSERT INTO employee_info SELECT * FROM employee_details; 

By executing above query, we can insert all details of employees in employee_details into another table employee_info. The employee_info table contains was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Insert all column values from existing table to another new table with specified rows.

Requirement – Insert all column values of employee_details table to another new employee_info table which are from department 2000. The query was as follows –

INSERT INTO employee_info 
SELECT emp_id,emp_name,designation, manager_id,date_of_hire, salary, dept_id 
FROM employee_details WHERE dept_id = 2000; 

By executing above query, we can insert all details of employees whose dept_id is 2000 into another table called employee_info. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Insert only specified column values into a new table.

Requirement – Fetch emp_id, emp_name, designation column values from employee_details table to existing table named as employee_info with column like emp_id, emp_name, designation, address. The query was as follows –

INSERT INTO employee_info(emp_id,emp_name,designation)
SELECT emp_id, emp_name, designation FROM employee_details; 

By executing above query, we can insert specified values into the employee_info table from employee_details table. The column that not specified will store either default value or NULL value. The output was as follows –

emp_id emp_name designation address
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst
006 Employee6 Analyst
007 Employee7 Clerk
008 Employee8 Salesman
009 Employee9 Salesman