SQL

INSERT INTO SELECT in SQL

📥 The INSERT INTO ... SELECT statement in SQL is used to copy data from one table into another table. Instead of manually inserting values, this statement inserts the result of a SELECT query into a target table. It is commonly used for data migration, backups, reporting, and archiving.

📖 What is INSERT INTO SELECT?

The INSERT INTO ... SELECT statement combines the functionality of the INSERT and SELECT statements. The SELECT query retrieves data, and the INSERTstatement stores that data in another table.

Information

The source and destination tables can be the same table or different tables, provided the selected columns are compatible with the destination columns.

🎯 Why Use INSERT INTO SELECT?

This statement is useful when you need to copy existing data instead of entering it manually.

📌 Copy data between tables.
📌 Archive historical records.
📌 Create backup tables.
📌 Populate reporting tables.
📌 Migrate data during database upgrades.

📊 Sample Tables

Consider the following Students table:

StudentID	Name	Department	Marks
101	Alice	Computer Science	92
102	Bob	Mathematics	85
103	Charlie	Physics	78
104	David	Computer Science	95

Destination table:

StudentID	Name	Department	Marks

📝 Basic Syntax

INSERT INTO SELECT Syntax

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

💡 Copy All Rows

Copy every record from one table to another.

Copy Entire Table

INSERT INTO GraduateStudents
(StudentID, Name, Department, Marks)
SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students;

After execution, every row from Students is copied into GraduateStudents.

📋 Copy Selected Rows

Use the WHERE clause to copy only records that satisfy a condition.

Copy Students with High Marks

INSERT INTO MeritStudents
(StudentID, Name, Department, Marks)
SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students
WHERE Marks >= 90;

📊 Copy Selected Columns

You can copy only specific columns instead of the entire row.

Copy Specific Columns

INSERT INTO StudentDirectory
(StudentID, Name)
SELECT StudentID,
       Name
FROM Students;

🔄 Copy Data Within the Same Table

You can also copy data into the same table when appropriate. Be careful to avoid inserting duplicate primary key values.

Copy Rows Within the Same Table

INSERT INTO StudentsArchive
(StudentID, Name, Department, Marks)
SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students
WHERE Department = 'Computer Science';

📈 INSERT INTO SELECT with JOIN

The SELECT statement can include joins before inserting data.

Insert Using JOIN

INSERT INTO StudentCourseReport
(StudentName, CourseName)
SELECT s.Name,
       c.CourseName
FROM Students s
INNER JOIN Courses c
ON s.StudentID = c.StudentID;

📊 INSERT INTO SELECT with Aggregate Functions

Aggregate queries can also be inserted into another table.

Insert Summary Data

INSERT INTO DepartmentSummary
(Department, AverageMarks)
SELECT Department,
       AVG(Marks)
FROM Students
GROUP BY Department;

⚖️ INSERT VALUES vs INSERT INTO SELECT

Feature	INSERT VALUES	INSERT INTO SELECT
Data Source	Manually supplied values.	Another query.
Multiple Rows	Yes.	Yes.
Copy Existing Data	❌ No	✅ Yes
Typical Use	Add new records.	Copy or migrate records.

💼 Real-World Example

A university wants to archive the records of students who scored at least 90 into a separate table for scholarship processing.

Create Scholarship List

INSERT INTO ScholarshipStudents
(StudentID, Name, Department, Marks)
SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students
WHERE Marks >= 90;

This query copies only eligible students into the ScholarshipStudents table.

⚠️ Common Mistakes

❌ Selecting a different number of columns than the destination table expects.
❌ Using incompatible data types.
❌ Copying duplicate primary key values.
❌ Forgetting to filter records when only a subset should be copied.

Warning

Ensure that the destination table's columns match the selected columns in both order and compatible data types. Also, verify that primary key and unique constraints are not violated during the insert.

⚠️ Best Practices

Best Practice

Always specify destination column names, use the WHERE clause to copy only the required records, verify data type compatibility before inserting, test the SELECT query independently before executing the insert, and use transactions when copying large or critical datasets.

🚀 Key Points to Remember

📌 INSERT INTO ... SELECT copies data from one query into another table.
📌 The destination and source tables can be different or, in some scenarios, the same.
📌 Source and destination columns must have compatible data types.
📌 The SELECT query can include WHERE, JOIN, GROUP BY, and aggregate functions.
📌 It is widely used for data migration, reporting, backups, and archiving.
📌 Test the SELECT query first to verify the rows that will be inserted.

>>"INSERT INTO SELECT moves data efficiently by combining the power of retrieval and insertion in a single SQL statement."

Summary

✅ The INSERT INTO ... SELECT statement is a powerful SQL feature for copying data between tables. It is commonly used for migrations, archiving, reporting, and bulk data processing, allowing you to transfer existing records quickly and efficiently without manually specifying values.