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.

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.

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

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 –

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 –

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 –