The SQL INSERT INTO SELECT statement is a powerful command for copying data from one table to another without having to manually insert the values. It’s commonly used for data migration, archiving, or populating a new table with data from an existing one. This technique can be very useful when you want to copy records from one table to another, potentially with transformations, filtering, or other conditions applied.
INSERT INTO SELECT Statement?The INSERT INTO SELECT statement is used to copy data from one table and insert it into another table. Unlike the INSERT INTO VALUES statement, which inserts specific data row by row, the INSERT INTO SELECT statement allows you to insert multiple rows of data by selecting them from another table.
The basic syntax for this statement is as follows:
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
target_table: The table where data will be inserted.column1, column2, ...: The columns in the target table where data will be inserted.source_table: The table from which data is being selected.WHERE condition: (Optional) A condition that specifies which rows to copy.One of the simplest use cases of the INSERT INTO SELECT statement is copying all data from one table to another with the same structure. Here's an example:
INSERT INTO employees_backup (id, name, position, salary)
SELECT id, name, position, salary
FROM employees;
This statement will insert all rows from the employees table into the employees_backup table, provided the column names and data types match between the two tables.
You don’t always need to insert data into all columns of the target table. You can specify a subset of columns to insert, as shown below:
INSERT INTO new_employees (name, position)
SELECT name, position
FROM employees
WHERE department = 'Engineering';
In this case, we're only inserting the name and position columns into the new_employees table for employees in the 'Engineering' department. The salary column is excluded in both the INSERT INTO and SELECT statements.
You can also modify or transform the data being inserted. For example, if you want to insert a record into the employees table, but with a 10% salary increase:
INSERT INTO employees (id, name, position, salary)
SELECT id, name, position, salary * 1.10
FROM employees
WHERE department = 'Marketing';
This statement inserts the id, name, position, and a 10% increased salary for employees in the 'Marketing' department.
If the target table is empty or doesn't exist yet, the INSERT INTO SELECT statement will work without issue, assuming the table is created with the correct structure beforehand. For example:
-- Create a new table with the same structure as 'employees'
CREATE TABLE employees_copy AS
SELECT * FROM employees WHERE 1=0;
-- Insert data into 'employees_copy' from 'employees'
INSERT INTO employees_copy
SELECT * FROM employees;
In this case, we first create an empty table employees_copy (by selecting no rows from the employees table with WHERE 1=0). Then, we insert all rows from employees into employees_copy.
INSERT INTO SELECT StatementYou can also use the INSERT INTO SELECT statement with JOINs to insert data from multiple tables into a target table. For example, if you want to insert employee details along with their department name from the employees and departments tables:
INSERT INTO employee_department_summary (employee_id, name, position, department_name)
SELECT e.id, e.name, e.position, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
This query inserts employee data along with the department name into the employee_department_summary table. It joins the employees and departments tables using the department_id field.
You can also use aggregate functions in the SELECT statement before inserting the data into the target table. For example, you may want to insert the average salary for each department into a new table:
INSERT INTO department_avg_salary (department_id, avg_salary)
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
This will insert the average salary for each department into the department_avg_salary table.
INSERT INTO SELECT StatementBefore using INSERT INTO SELECT, make sure that the columns in the source table match the columns in the target table in terms of data types and structure. If there are any discrepancies (e.g., incompatible data types), the query will fail.
WHERE Clauses for Selective Data InsertionIf you only want to insert specific rows from the source table, always use a WHERE clause to filter the data you’re copying. This helps prevent inserting unwanted records and ensures that only relevant data is transferred.
For example:
INSERT INTO employees_backup (id, name, position, salary)
SELECT id, name, position, salary
FROM employees
WHERE hire_date < '2020-01-01';
This will insert only employees who were hired before January 1, 2020.
If you are inserting a large amount of data, it’s good practice to use transactions to ensure data integrity. This way, if something goes wrong, you can roll back the transaction.
BEGIN TRANSACTION;
INSERT INTO employees_backup (id, name, position, salary)
SELECT id, name, position, salary
FROM employees;
COMMIT;
If any errors occur during the INSERT INTO SELECT operation, you can roll back the transaction, preventing partial data insertion.
INSERT INTO SELECT StatementINSERT INTO clause matches the number of columns selected in the SELECT statement.NOT NULL have values. You might need to modify your SELECT statement to exclude or provide default values for those columns.