Insert into Select

Insert Into Select in MySQL

Introduction

Sometimes you need to copy data from one table to another instead of manually inserting values.

In MySQL, this is done using the INSERT INTO SELECT statement.

It allows you to insert data into a table directly from another table or query result.

What is INSERT INTO SELECT

The INSERT INTO SELECT statement is used to:

• Copy data from one table
• Insert query results into another table
• Transfer data between tables

Basic Syntax

INSERT INTO table2 (column1, column2)

SELECT column1, column2

FROM table1;

Example

INSERT INTO Students_backup (id, name, age)

SELECT id, name, age

FROM Students;

This copies all data from Students to Students_backup.

Insert with Condition

You can copy filtered data:

INSERT INTO Students_backup (id, name, age)

SELECT id, name, age

FROM Students

WHERE age > 20;

Insert Specific Columns

INSERT INTO Students_backup (name)

SELECT name

FROM Students;

Important Rules

• Data types must match between tables
• Number of columns must be the same
• Column order should match

Example Scenario

Suppose you want to create a backup of selected students:

INSERT INTO TopStudents

SELECT *

FROM Students

WHERE marks > 80;

Advantages

• Fast data copying
• No need for manual insertion
• Useful for backups and migrations
• Supports filtering and transformation

Common Mistakes

• Mismatched column order
• Different data types
• Forgetting the WHERE condition
• Copying unwanted data

Key Points to Remember

• INSERT INTO SELECT copies data between tables
• Works with full or partial data
• Supports conditions and filters
• Requires matching column structure
• Useful for backups and data transfer