INSERT INTO Command

What INSERT Does

INSERT adds new rows to a table. It is a data manipulation language statement: the table structure already exists, and the statement supplies values that must satisfy its data types and constraints.

The most reliable basic form is:

INSERT INTO table_name (column_1, column_2)
VALUES (value_1, value_2);

Columns and values match by position. Text and date literals use single quotes. Numeric literals do not. SQL keywords can be uppercase for readability.

Insert One Row

USE coding_school;

INSERT INTO students
  (full_name, class_name, marks, status)
VALUES
  ('Aarav', 'X-A', 86.50, 'Active');

student_id is omitted because MySQL generates it with AUTO_INCREMENT. created_at is omitted so its CURRENT_TIMESTAMP default runs. The explicit column list makes both decisions visible.

SELECT LAST_INSERT_ID() AS new_student_id;

Run LAST_INSERT_ID() in the same session immediately after the successful insert. Application drivers normally expose the generated ID directly.

Insert Multiple Rows Efficiently

INSERT INTO students
  (full_name, class_name, marks, status)
VALUES
  ('Meera', 'X-A', 91.00, 'Active'),
  ('Kabir', 'X-B', 74.00, 'Inactive'),
  ('Sana',  'X-B', 88.50, 'Active');

A multi-row statement reduces client/server round trips and clearly shows that the rows belong to one load. Every parenthesized group must contain the same number of values in the same column order.

Defaults, NULL and Omitted Columns

These three forms are related but not identical:

-- status is omitted, so its default 'Active' is used
INSERT INTO students (full_name, class_name, marks)
VALUES ('Ishaan', 'XI-A', 82.00);

-- marks explicitly has no known value
INSERT INTO students (full_name, class_name, marks, status)
VALUES ('Naina', 'XI-A', NULL, 'Active');

-- DEFAULT explicitly requests the declared default
INSERT INTO students (full_name, class_name, marks, status)
VALUES ('Vihaan', 'XI-B', 79.50, DEFAULT);

• Omitted column: MySQL uses its default, an automatic value or NULL if permitted.
• DEFAULT: explicitly requests the column default.
• NULL: stores no known value and fails if the column is NOT NULL.
• Empty string: is a known zero-length string, not NULL.

Insert Rows from a SELECT Query

INSERT ... SELECT copies query results into a compatible destination. It is useful for archive, summary or migration tasks.

CREATE TABLE active_students LIKE students;

INSERT INTO active_students
  (student_id, full_name, class_name, marks, status, created_at)
SELECT
  student_id, full_name, class_name, marks, status, created_at
FROM students
WHERE status = 'Active';

The SELECT output column count and order must match the INSERT column list. If this job may run again, decide how duplicate keys should be handled instead of assuming the second run is safe.

Verify Results and Understand Errors

SELECT student_id, full_name, class_name, marks, status
FROM students
ORDER BY student_id;

SELECT ROW_COUNT() AS rows_affected;

Many clients already display affected rows. ROW_COUNT() must be called immediately after the relevant data-changing statement in the same session.

Application Safety: Never Build SQL by Concatenation

Values typed by users must not be joined directly into an INSERT string. Use a prepared statement with placeholders through your language's MySQL driver:

INSERT INTO students
  (full_name, class_name, marks, status)
VALUES (?, ?, ?, ?);

The application binds values separately. Prepared statements prevent input from being interpreted as SQL syntax and also make type handling clearer. Validate business rules in the application, keep database constraints, use least-privilege credentials and do not expose database errors to visitors.

Practice

1. Insert one new student while allowing status and created_at to use defaults.
2. Insert three books in one statement using an explicit column list.
3. Insert a student whose marks are unknown; explain why NULL is more accurate than zero.
4. Preview and then write an INSERT ... SELECT that archives inactive students.
5. Cause a CHECK or duplicate-key error in a disposable database and explain what protected the data.

Next, read the stored rows with the SELECT command.

Quick Summary

• INSERT INTO ... (columns) VALUES (...) adds rows.
• Always prefer an explicit column list.
• Multi-row INSERT reduces round trips and uses one consistent column order.
• Omitted columns, DEFAULT, NULL and empty strings have different meanings.
• Constraints protect quality; prepared statements protect applications.

Official References

• INSERT Statement — MySQL 8.4
• Using AUTO_INCREMENT
• LAST_INSERT_ID()
• Prepared Statements

References reviewed on 14 August 2026. Test imports on a copy and back up valuable data first.