MySQL INSERT INTO SELECT Statement

MySQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement in MySQL is used to copy data from one table to another in a single SQL query.

 Very useful for data migration, backup, reporting tables, and ETL tasks.

 What is INSERT INTO SELECT?

• Inserts data from another table

• Uses a SELECT query as the data source

• No need to write values manually

 Think of it as: copy data using SELECT + INSERT together

 Basic Syntax

 Simple Example

Copy All Rows

•  Both tables must have same number of columns and compatible data types.

 Insert Selected Columns

You can insert values into specific columns only instead of all columns.
Columns not mentioned will take their default values or NULL.

Syntax

Example

 Insert with WHERE Condition

INSERT does not directly support WHERE.
To insert rows conditionally, you must use INSERT INTO … SELECT with WHERE.

Example

 Insert from Multiple Tables (JOIN)

Data can be inserted using a SELECT statement that joins multiple tables.
This is useful for combining data from related tables.

Example

Insert with Calculated Values

You can insert values calculated using expressions or functions.
The calculation happens during insertion.

Example

 Insert with DEFAULT / AUTO_INCREMENT

Columns with DEFAULT values are automatically filled if omitted.
AUTO_INCREMENT columns generate values automatically.

Example

 Insert IGNORE

INSERT IGNORE skips rows that cause duplicate key or constraint errors.
The query continues without stopping on errors.

Example

INSERT INTO SELECT vs INSERT VALUES (Interview)

Common Errors & Fixes

Interview Questions & MCQs (Very Important)

Q1. What does INSERT INTO SELECT do?

A) Inserts manual values
B) Copies data from another table
C) Deletes data
D) Updates table

Answer: B

Q2. Which clause is mandatory in INSERT INTO SELECT?

A) VALUES
B) WHERE
C) SELECT
D) GROUP BY

Answer: C

Q3. Can INSERT INTO SELECT use WHERE clause?

A) Yes
B) No

Answer: A

Q4. Can we insert data from multiple tables?

A) Yes (using JOIN)
B) No

Answer: A

Q5. What happens if column count doesn’t match?

A) Data truncated
B) NULL inserted
C) Error occurs
D) Query ignored

Answer: C

Q6. Which keyword skips duplicate errors?

A) SKIP
B) IGNORE
C) SAFE
D) CHECK

Answer: B

Q7. Does INSERT INTO SELECT copy table structure?

A) Yes
B) No

Answer: B
(Only data is copied)

 Real-Life Use Cases

•  Backup tables
•  Archiving old records
•  Data migration
•  Reporting tables
•  ETL processes

 Summary

• INSERT INTO SELECT copies data between tables

• Supports WHERE, JOIN, calculations

• Faster than row-by-row inserts

• Does not copy structure

• Very important for SQL exams & interviews