SQL INSERT INTO SELECT

The INSERT INTO ... SELECT statement in SQL allows you to insert data into one table based on a query that selects data from another table. This is particularly useful for copying data between tables or merging datasets.

Basic Syntax

INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

• target_table: The table where you want to insert data.
• column1, column2, column3, ...: The columns in the target table where data will be inserted.
• source_table: The table from which data is being selected.
• condition: An optional condition to filter which rows from the source table are selected.

Example

Suppose we have two tables:

1. employees - This table stores employee information.
2. employees_archive - This table is used to archive employee data.

Table Definitions

employees:

SQL Operation

To archive all employees from the HR department into the employees_archive table:

INSERT INTO employees_archive (id, name, department, hire_date)
SELECT id, name, department, hire_date
FROM employees
WHERE department = 'HR';

Result

After executing the query, the employees_archive table will look like this:

Key Points

• Ensure that the columns you are selecting match the columns in the target table in both number and data type.
• The SELECT statement can include joins, aggregations, and other SQL operations to manipulate the data before inserting it.

This method is powerful for tasks such as data migration, backup, or transforming datasets within your database.