The INSERT INTO SELECT statement in MySQL allows you to insert data from an existing table into a new table. The new table can have a different structure from the original table, as long as the columns selected in the SELECT statement match the columns in the new table.
The syntax for the INSERT INTO SELECT statement is as follows:
INSERT INTO new_table (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM old_table WHERE condition;
In this syntax, the new_table is the name of the new table that you want to insert the data into. The column1, column2, and so on, represent the columns in the new table that you want to insert the data into. The old_table is the name of the existing table that you want to select data from. The condition is an optional condition that filters the data that is selected from the old_table.
For example, suppose you have a table called employees with columns employee_id, first_name, last_name, salary, and hire_date, and you want to create a new table called new_employees that includes only the employee_id, first_name, and last_name columns from employees. You can use the INSERT INTO SELECT statement as follows:
CREATE TABLE new_employees ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50) ); INSERT INTO new_employees (employee_id, first_name, last_name) SELECT employee_id, first_name, last_name FROM employees;
This will create a new table called new_employees with the columns employee_id, first_name, and last_name, and insert the corresponding data from the employees table.
It’s important to note that the columns selected in the SELECT statement must match the data types and order of the columns in the new table, and that any unique constraints or indexes on the new table may cause the INSERT INTO SELECT statement to fail if the selected data violates those constraints.
