SQL INSERT INTO SELECT Statement
SQL INSERT INTO SELECT Statement
The SQL INSERT INTO SELECT statement is used to insert data from one table into another table using a SELECT statement. It allows you to combine the SELECT and INSERT INTO statements into a single statement, which can be useful for copying data from one table to another or for creating a backup table.
The syntax for the INSERT INTO SELECT statement is as follows:
INSERT INTO destination_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
In this syntax, the destination_table is the name of the table where the data will be inserted, and the source_table is the name of the table from which the data will be retrieved. The SELECT statement specifies the columns that will be inserted into the destination table, and the WHERE clause is used to filter the result set if necessary.
Here’s an example of using the INSERT INTO SELECT statement to copy data from one table to another:
INSERT INTO employees_backup (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id
FROM employees
WHERE department_id = 50;
In this example, data from the employees table is copied into a backup table named employees_backup, but only for employees who work in department 50.
Note that the columns in the destination table must match the columns in the SELECT statement in terms of name, data type, and order. If the columns do not match, you will receive an error.
Also, be careful when using the INSERT INTO SELECT statement, as it can potentially overwrite data in the destination table. Always ensure that you have a backup of the destination table before running the statement.