SQL INSERT INTO SELECT Statement

Overview

The INSERT INTO SELECT statement allows you to insert rows into a table by selecting data from another table. This is particularly useful for copying data between tables or merging data from different sources.

Syntax

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

Example 1: Basic Usage

To copy all data from the Employees table into a new table called EmployeesArchive:

INSERT INTO EmployeesArchive (EmployeeID, EmployeeName, Email, Department)
SELECT EmployeeID, EmployeeName, Email, Department
FROM Employees;

This statement copies all rows from the Employees table into the EmployeesArchive table.

Example 2: With Conditions

You can also filter the data being inserted by using a WHERE clause. For example, to insert only employees from the Sales department:

INSERT INTO EmployeesArchive (EmployeeID, EmployeeName, Email, Department)
SELECT EmployeeID, EmployeeName, Email, Department
FROM Employees
WHERE Department = 'Sales';

This statement copies only the rows of employees from the Sales department into the EmployeesArchive table.

Example 3: Inserting into Specific Columns

You can choose specific columns to insert data into. For instance, if the EmployeesArchive table has only EmployeeName and Email columns:

INSERT INTO EmployeesArchive (EmployeeName, Email)
SELECT EmployeeName, Email
FROM Employees
WHERE Department = 'HR';

This will insert only the names and email addresses of employees from the HR department into the EmployeesArchive table.

Important Notes

• Ensure that the data types of the selected columns match the data types of the target columns.
• If the target table already contains data, the new rows will be added below the existing rows.
• Using the INSERT INTO SELECT statement can be an efficient way to copy large amounts of data.

Conclusion

The INSERT INTO SELECT statement is a powerful SQL feature that allows you to efficiently insert data from one table into another, with the flexibility to filter and select specific columns. It is a key tool for data manipulation and migration in SQL databases.