INSERT INTO in SQL: Single Row, Multiple Rows, and INSERT SELECT

A table without data is just an empty structure — INSERT INTO is the statement that actually puts rows into it. Whether you're adding one new customer, importing a thousand products at once, or copying filtered data from one table into another, INSERT INTO is the starting point for every piece of data in a relational database. This lesson covers all three core patterns: single-row, multi-row, and INSERT SELECT.

What Is INSERT INTO?

INSERT INTO is the DML (Data Manipulation Language) statement used to add new rows to a table. It can insert a single row, multiple rows in one statement, or even copy rows directly from the result of a SELECT query on another table, without needing to extract and re-insert the data manually.

What You'll Learn

• Insert a single row using full and column-specified INSERT INTO syntax.
• Insert multiple rows efficiently in one statement.
• Copy data between tables using INSERT INTO ... SELECT.
• Understand how DEFAULT and AUTO_INCREMENT columns behave during INSERT.

Key Terms to Know

• DML: Data Manipulation Language, the category of SQL statements (INSERT, UPDATE, DELETE) that work with row data rather than table structure.
• INSERT INTO: The statement used to add new rows to a table.
• Bulk insert: Inserting multiple rows in a single INSERT statement for efficiency.
• INSERT SELECT: A pattern that inserts rows into one table directly from a SELECT query on another.

Single-Row INSERT: With and Without Column Names

The safest form explicitly lists column names: INSERT INTO students (full_name, email) VALUES ('Aarav Shah', 'aarav@example.com');. This works correctly even if the table's column order changes later, since values are matched by name, not position.

Omitting column names, like INSERT INTO students VALUES (NULL, 'Aarav Shah', 'aarav@example.com', '2005-04-12');, requires providing a value for every single column in the table's exact defined order, including AUTO_INCREMENT columns (where NULL or 0 lets MySQL auto-generate the value). This shorthand is more fragile and breaks silently if the table structure changes, so explicit column names are the safer everyday habit.

Multi-Row INSERT: Adding Several Rows at Once

MySQL allows multiple value sets in a single INSERT statement: INSERT INTO students (full_name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com'), ('C', 'c@x.com');. This is significantly faster than running three separate INSERT statements, since MySQL only has to parse the statement and write to disk once instead of three times.

This pattern is the standard approach for seeding initial data, importing data from spreadsheets, or any situation where many known rows need to be added together.

INSERT SELECT: Copying Data Between Tables

INSERT INTO target_table (col1, col2) SELECT col_a, col_b FROM source_table WHERE condition; copies rows directly from one table into another, applying any filtering, joins, or transformations the SELECT query supports. No intermediate extraction step is needed — the data moves directly inside the database engine.

This pattern is extremely common for archiving old records into a separate table, populating a reporting table from raw transactional data, or migrating validated staging data into its permanent destination table.

Visual Summary

Picture INSERT INTO as filling out forms and dropping them into a filing tray (table). Single-row insert is dropping in one completed form. Multi-row insert is dropping in a small stack of forms in one trip instead of walking back and forth. INSERT SELECT is photocopying selected forms already sitting in a different tray and dropping the copies straight into this one, without manually re-typing anything.

INSERT INTO Patterns Compared

Pattern	Use Case	Performance
Single-row INSERT	Adding one new record	Fine for occasional inserts
Multi-row INSERT	Seeding/importing several known rows	Much faster than repeated single inserts
INSERT SELECT	Copying/filtering data from another table	Efficient, avoids extracting data manually

SQL Example

-- Single-row insert with explicit column names
INSERT INTO students (full_name, email, date_of_birth)
VALUES ('Aarav Shah', 'aarav.shah@example.com', '2005-04-12');

-- Multi-row insert in one statement
INSERT INTO students (full_name, email, date_of_birth) VALUES
  ('Meera Iyer', 'meera.iyer@example.com', '2004-11-02'),
  ('Rohan Verma', 'rohan.verma@example.com', '2005-07-19'),
  ('Diya Nair', 'diya.nair@example.com', '2006-01-30');

-- INSERT SELECT: copy active students into an archive table
INSERT INTO students_archive (full_name, email, archived_on)
SELECT full_name, email, CURRENT_DATE
FROM students
WHERE enrolled_since < '2020-01-01';

The first statement adds one student with explicit column names, making it immune to future column reordering. The second adds three students in a single efficient statement. The third copies students enrolled before 2020 into an archive table, computing archived_on inline as the current date — no application code or manual data export was needed.

Real-World Examples

• E-commerce platforms use multi-row INSERT when importing a bulk product catalog from a supplier feed.
• Analytics pipelines use INSERT SELECT to populate daily summary tables directly from raw event tables inside the database.
• User registration flows use single-row INSERT for every new signup, one row at a time, in real time.
• Data archival jobs use INSERT SELECT followed by DELETE to move old records into a cold-storage table without ever exporting data outside the database.

Best Practices and Pro Tips

• Always specify column names explicitly in INSERT statements, even when inserting every column — it protects against silent breakage if the table structure changes later.
• Prefer a single multi-row INSERT over many single-row INSERT statements when adding several known rows at once; it's noticeably faster, especially over a network connection to the database.
• When using INSERT SELECT, double-check that the SELECT's column order and types match the target table's expected columns exactly to avoid inserting data into the wrong columns.

Common Mistakes to Avoid

• Omitting column names and assuming MySQL will 'figure out' which value goes where — it always matches by position, not by guessing.
• Running hundreds of separate single-row INSERT statements in a loop instead of batching them into fewer multi-row statements, causing unnecessary slowdown.
• Forgetting that INSERT SELECT requires the target table to already exist with compatible columns — it doesn't create the table automatically.
• Not handling potential UNIQUE or FOREIGN KEY constraint violations, causing an entire multi-row INSERT to fail partway through.

Interview Questions

Q1. What is the difference between a single-row INSERT and a multi-row INSERT?

A single-row INSERT adds exactly one new row per statement. A multi-row INSERT adds several rows in one statement by listing multiple comma-separated value sets after VALUES, which is more efficient than running multiple single-row inserts.

Q2. What does INSERT INTO ... SELECT do?

It inserts rows into a target table using the result of a SELECT query run against a source table (or tables), copying and optionally filtering or transforming data directly within the database, without an external extraction step.

Q3. Why is it considered best practice to specify column names in an INSERT statement?

Explicitly naming columns ensures values are matched correctly by name rather than position, protecting the statement from breaking silently if the table's column order changes in the future.

Q4. What happens to an AUTO_INCREMENT column if you don't include it in an INSERT statement's column list?

MySQL automatically generates the next sequential value for that column, since AUTO_INCREMENT columns don't require an explicit value during INSERT.

Practice MCQs

1. Which statement correctly inserts a single row into a students table?

1. UPDATE students SET full_name = 'A';
2. INSERT INTO students (full_name) VALUES ('A');
3. SELECT full_name FROM students;
4. DELETE FROM students WHERE full_name = 'A';

Answer: B. INSERT INTO students (full_name) VALUES ('A');

Explanation: INSERT INTO with explicit column names and VALUES is the correct syntax for adding a single new row.

2. Which pattern copies data from one table into another without exporting it externally?

1. Multi-row INSERT
2. INSERT SELECT
3. Single-row INSERT
4. UPDATE

Answer: B. INSERT SELECT

Explanation: INSERT INTO ... SELECT directly copies rows from a source table's query result into a target table within the database.

3. Why is a multi-row INSERT generally faster than several single-row INSERT statements?

1. It uses less storage space
2. It avoids parsing and writing once per row, doing it once for the whole batch
3. It skips constraint checks
4. It automatically creates indexes

Answer: B. It avoids parsing and writing once per row, doing it once for the whole batch

Explanation: Batching multiple rows into one INSERT statement reduces the per-statement overhead compared to issuing many separate single-row inserts.

Quick Revision Points

• INSERT INTO is a DML statement used to add new rows to a table.
• Multi-row INSERT batches several rows into one statement for better performance.
• INSERT SELECT copies rows from a source table's query result directly into a target table.
• Always prefer explicit column names over relying on positional VALUES matching.

Conclusion

• INSERT INTO is the starting point for every row of data that will ever exist in a table.
• Batching multi-row inserts is a simple, high-impact performance habit.
• INSERT SELECT eliminates the need to manually extract and re-insert data between tables.

INSERT INTO is SQL's mechanism for adding data, supporting single-row inserts for everyday additions, multi-row inserts for efficient batch loading, and INSERT SELECT for moving or copying data directly between tables. Mastering all three patterns, along with explicit column naming, sets up a strong foundation for the SELECT-focused lessons that follow, where this same data will be queried, filtered, and sorted.