DbSchema Database Designer

DbSchema Tutorial | SQL INSERT INTO SELECT STATEMENT

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 !!

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys.


Query Builder alt >

Query Builder

Create SQL Queries using the mouse.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.