The SQL INSERT INTO SELECT statement is used to insert data into a table from the result of a SELECT statement. The INSERT INTO SELECT statement allows you to copy data from one table and insert it into another table, making it a very useful tool for data migration and manipulation.
Syntax
The syntax of the SQL INSERT INTO SELECT statement is as follows:
INSERT INTO table_name (column1, column2, ...) SELECT column1, column2, ... FROM source_table_name WHERE condition;
In the above syntax, “table_name” is the name of the table to insert data into, “column1”, “column2”, etc. represent the columns to be inserted, and “source_table_name” is the name of the table from which the data is being selected. The “condition” in the “WHERE” clause is used to filter the rows to be inserted into the target table.
Example
Consider the following two tables:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | 5500 |
| 3 | Bob Smith | 6000 |
| 4 | Alice Johnson | 6500 |
Table “employees”
| ID | Name | Salary |
|---|---|---|
| 5 | John Doe | 8000 |
| 6 | Jane Doe | 9000 |
| 7 | Bob Smith | 9500 |
| 8 | Alice Johnson | 10000 |
Table “high_salary_employees”
Now, if we want to insert all the rows from the “high_salary_employees” table into the “employees” table, we can use the following SQL statement:
INSERT INTO employees (Name, Salary) SELECT Name, Salary FROM high_salary_employees;
The above statement will insert all the rows from the “high_salary_employees” table into the “employees” table, preserving the values in the “Name” and “Salary” columns. The ID column in the “employees” table will be automatically incremented, as it is the primary key of the table.
If we only want to insert certain rows based on a condition, we can use a “WHERE” clause in the SELECT statement. For example, if we only want to insert the rows where the salary is greater than 9000, we can use the following SQL statement:
INSERT INTO employees (Name, Salary) SELECT Name, Salary FROM high_salary_employees WHERE Salary > 9000;
The above statement will only insert the rows where the salary is greater than 9000, resulting in the insertion of two rows into the “employees” table.
Conclusion
In conclusion, the SQL INSERT INTO SELECT statement is a very useful tool for data migration and manipulation. It allows you to copy data from one table and insert it into another table, making it a powerful tool for data management and organization. Whether you want to insert all the rows or only a few based on a condition, the INSERT INTO SELECT statement provides a simple and efficient way to do so.
