SQL INSERT INTO Statement — Adding Data to SQL Tables

A new student joins class 9B. The office must add a row without accidentally putting a city into the name column.

INSERT adds rows to a table. An explicit column list connects each supplied value to the intended field.

Match the values to the listed columns in the same order. Omitted columns receive defaults or NULL only when the schema permits that outcome. A multi-row INSERT can add several records in one statement, but constraints still apply to the rows being inserted.

Related concepts: Create Tables and Data Constraints.

Tables are like empty registers — they need entries to become useful. That’s where the INSERT INTO statement steps in. It lets you add rows of meaningful data into your SQL tables — student records, teacher assignments, exam scores — all start here.

SQL · portable concepts; preserved examples identify their engine

Standard Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Short Syntax (Only when inserting all columns in order)

INSERT INTO table_name
VALUES (value1, value2, ...);

SQL · portable concepts; preserved examples identify their engine

Assume we already created a students table:

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

Now let’s insert data for a student named Arjun:

INSERT INTO students (roll_no, name, class, age, city)
VALUES (1, 'Arjun Deshmukh', '10A', 15, 'Pune');

Table Contents after INSERT

SELECT * FROM students;

SQL · portable concepts; preserved examples identify their engine

Rather than inserting one record at a time, you can insert many in a single statement:

INSERT INTO students (roll_no, name, class, age, city)
VALUES
(2, 'Priya Reddy', '9B', 14, 'Hyderabad'),
(3, 'Karan Mehta', '10A', 15, 'Ahmedabad'),
(4, 'Neha Iyer', '8C', 13, 'Chennai');

Table Contents after INSERT

SELECT * FROM students;

SQL · portable concepts; preserved examples identify their engine

If you don't have data for all columns, you can skip them — but you must exclude them in the column list as well:

INSERT INTO students (roll_no, name, class)
VALUES (5, 'Devanshi Shah', '9A');

Table Contents after INSERT

SELECT * FROM students;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Let’s try inserting without specifying roll_no (which is PRIMARY KEY):

INSERT INTO students (name, class, age, city)
VALUES ('Ishaan Pandey', '10B', 15, 'Lucknow');

ERROR: Column 'roll_no' cannot be null

This MySQL example uses a primary key without an automatic value, so strict-mode MySQL rejects the missing key. The displayed error wording can differ by version. A required value may also be supplied by a declared default or an automatic key generator; SQLite can generate a value for an INTEGER PRIMARY KEY. Check the actual table definition and engine.

This is a separate, fresh students table with a city default. Do not run CREATE TABLE on the already-created table from the preceding examples.

SQL · portable concepts; preserved examples identify their engine

Suppose we use the students table, where the city column has a default value of 'Delhi':

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10),
  age INT,
  city VARCHAR(30) DEFAULT 'Delhi'
);

Now insert a student record without specifying the city column:

INSERT INTO students (roll_no, name, class, age)
VALUES (1, 'Mehul Arora', '10A', 15);

Table Contents after INSERT

SELECT * FROM students;

Since no city was provided, SQL automatically filled in the default value 'Delhi'.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• Always specify column names for clarity and future safety.
• Validate data types — the engine may reject, convert or store a mismatched value, depending on its type rules and settings.
• Insert in batches if you have many records.

Do not assume a missing value becomes zero or an empty string. A missing required key, repeated unique value or invalid foreign key can reject the operation. AUTO_INCREMENT examples belong to MySQL; other engines use different identity mechanisms.

Add Saira with roll number 4, then show the new row.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

INSERT INTO practice_students VALUES (4, 'Saira', '9B', 92, 'Mumbai');
SELECT roll_no, name, class, marks, city FROM practice_students WHERE roll_no = 4;

The new record has its own roll number. Filtering by that key verifies the inserted values without relying on an unspecified table display order.

INSERT adds rows to a table. An explicit column list connects each supplied value to the intended field.

The new record has its own roll number. Filtering by that key verifies the inserted values without relying on an unspecified table display order.

Continue your understanding with Create Tables and Data Constraints.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.