DbSchema Tutorial | SQL INSERT INTO SELECT STATEMENT

Publish on DbSchema Blog >>>

In the vast world of SQL, the ability to migrate, manipulate, and populate data is of paramount importance. The INSERT INTO SELECT statement is a versatile tool that facilitates such tasks. Dive into this guide to grasp its potential and intricacies.

1. Introduction to SQL INSERT INTO SELECT STATEMENT

The INSERT INTO SELECT statement in SQL is used to insert data from one table (source) into another table (destination), making it useful for tasks like data archival, backups, or populating test tables from production data.

Syntax:

INSERT INTO destination_table
SELECT column1, column2, ...
FROM source_table
WHERE condition;

2. Differentiating Between INSERT INTO and INSERT INTO SELECT

Aspect	INSERT INTO	INSERT INTO SELECT
Purpose	Inserts new rows into a table.	Inserts data from one table into another.
Data Source	Directly provided values.	Values fetched from another table.
Number of Tables Involved	One	At least two (source and destination)

3. Key Concepts and Examples

Insert Data from All Columns of Source Table to Destination Table

Example:

Given a students table:

ID	Name
1	Alice
2	Bob

To copy all data from students to backup_students:

INSERT INTO backup_students
SELECT * FROM students;

Resultant backup_students table:

ID	Name
1	Alice
2	Bob

Explanation:

All rows from the students table are copied to the backup_students table.

Insert Rows from Source to Destination Table by Specifying Column Names

Example:

Using the students table, we want to insert only the Name column into student_names.

INSERT INTO student_names (Name)
SELECT Name FROM students;

Resultant student_names table:

Name
Alice
Bob

Explanation:

We selected only the names from students and inserted them into student_names.

Insert Using Both Columns and Defined Values

Example:

Using the students table, we want to insert names into a student_feedback table with a static column Feedback set to ‘No Feedback’.

INSERT INTO student_feedback (Name, Feedback)
SELECT Name, 'No Feedback' FROM students;

Resultant student_feedback table:

Name	Feedback
Alice	No Feedback
Bob	No Feedback

Explanation:

Each student’s name, along with the static value ‘No Feedback’, is inserted into student_feedback.

INSERT INTO SELECT Statement with Join Clause

Example:

Given students:

ID	Name
1	Alice
2	Bob

And grades:

Student_ID	Grade
1	A
2	B

We create a student_report:

INSERT INTO student_report (Name, Grade)
SELECT students.Name, grades.Grade
FROM students
JOIN grades
ON students.ID = grades.Student_ID;

Resultant student_report table:

Name	Grade
Alice	A
Bob	B

Explanation:

A JOIN operation merges students and grades on matching IDs, and the data is inserted into student_report.

Insert into Select Statement with WHERE Condition

Example:

Using the students table:

ID	Name
1	Alice
2	Bob
3	Eve

We insert students with ID > 1 into senior_students:

INSERT INTO senior_students (ID, Name)
SELECT ID, Name FROM students
WHERE ID > 1;

Resultant senior_students table:

ID	Name
2	Bob
3	Eve

Explanation:

Only students with IDs greater than 1 are inserted into senior_students.

4. Common Mistakes

1. Mismatched Columns: Ensure columns in the SELECT statement match the destination table.
2. Ignoring Data Types: Source and destination columns should have compatible data types.
3. Not Using WHERE Properly: Without WHERE, all data gets copied, which might not be the intention.

5. Frequently Asked Questions

1. Can I use INSERT INTO SELECT across databases?
   Yes, if both databases are on the same server and permissions are granted.

2. What if source and destination tables have different column names?
   Column names don’t have to match. Ensure order and data types are compatible.

6. Practice Questions

1. Insert the names of students with ID > 5 from students into advanced_students.
2. Backup the employees table, but only those employed for over 10 years.
3. Using orders and products, insert orders for a product into special_orders.
4. Insert the top 10% of highest-scoring students from grades into honors_students.

7.Conclusion

In conclusion, the INSERT INTO SELECT statement is a vital SQL tool for efficient data operations. This guide offers an in-depth look to empower your database tasks. Practice and explore to master its capabilities!

Happy Querying !!