SQL INSERT INTO SELECT Statement

Welcome to The Coding College! In this tutorial, we’ll explore the SQL INSERT INTO SELECT Statement, a powerful way to copy data from one table to another. By the end of this guide, you’ll understand its syntax, use cases, and practical examples to use it effectively in your database operations.

What Is SQL INSERT INTO SELECT?

The SQL INSERT INTO SELECT statement is used to copy data from one table into another table. Unlike the SELECT INTO statement, which creates a new table, the INSERT INTO SELECT statement requires an existing target table.

Syntax

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

Key Features

1. Requires Existing Target Table: The target_table must already exist in the database.
2. Customizable Columns: Allows selective insertion of specific columns into the target table.
3. Conditional Data Copy: You can filter rows from the source table using a WHERE clause.

Example Tables

Table 1: employees

Table 2: high_earners (Target Table)

Examples

Example 1: Insert All Data

Query: Copy All Employee Data to high_earners

INSERT INTO high_earners (employee_id, name, salary)  
SELECT employee_id, name, salary  
FROM employees;  

Result

Example 2: Insert Data with Conditions

Query: Insert Employees with Salary > 75000

INSERT INTO high_earners (employee_id, name, salary)  
SELECT employee_id, name, salary  
FROM employees  
WHERE salary > 75000;  

Result

Example 3: Insert Data with Modified Columns

Query: Add Employees from IT Department with Modified Salary

INSERT INTO high_earners (employee_id, name, salary)  
SELECT employee_id, name, salary * 1.1  
FROM employees  
WHERE department = 'IT';  

Explanation

• The salary is increased by 10% during the insertion process.

Result

Advanced Use Cases

Combining Data from Multiple Tables

INSERT INTO high_earners (employee_id, name, salary)  
SELECT e.employee_id, e.name, d.avg_salary  
FROM employees e  
JOIN departments d  
ON e.department = d.department  
WHERE e.salary > d.avg_salary;  

Explanation

• This query inserts employee details where their salary exceeds their department’s average salary.

Common Errors

1. Column Mismatch: Ensure the number of columns in the target_table matches the columns in the SELECT query.
2. Data Type Mismatch: The data types of columns in the source and target tables should be compatible.
3. Constraint Violations: Ensure that the data being inserted does not violate constraints like primary keys or foreign keys.

Best Practices

1. Test Before Execution: Run the SELECT query separately to validate the data being inserted.
2. Use Explicit Column Names: Avoid SELECT * for better clarity and control.
3. Backup Data: Always backup critical data before performing insertions.

INSERT INTO SELECT vs SELECT INTO

Conclusion

The SQL INSERT INTO SELECT statement is a versatile tool for transferring data between tables. Whether you’re copying data for reporting, backups, or creating new datasets, this feature ensures efficiency and flexibility.

For more SQL tutorials and resources, visit The Coding College, your trusted partner for mastering coding and programming!