SQL INSERT INTO

Once a table is created, the next step is to add data into it. The INSERT INTO statement is used to add new rows of data into a table. Each time INSERT INTO runs, one or more new records are added to the table.

Think of it like filling out a form and submitting it — each submission becomes a new row in the table.

Syntax of INSERT INTO

There are two ways to write the INSERT INTO statement.

Method 1: Specifying Column Names (Recommended)

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

This method lists the column names first, then provides the corresponding values. This approach is safer and more readable because it is clear which value belongs to which column.

Method 2: Without Specifying Column Names

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

This method skips listing column names and provides values in the exact order the columns were defined in the table. All columns must receive a value — otherwise an error will occur.

The Students Table Used in Examples

The following table structure will be used for all examples in this topic:

CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Age         INT,
    City        VARCHAR(30),
    JoinDate    DATE
);

Inserting a Single Row

INSERT INTO Students (StudentName, Age, City, JoinDate)
VALUES ('Ravi Sharma', 20, 'Delhi', '2024-01-10');

Notice that StudentID is not listed in the column names because it is set to AUTO_INCREMENT — the database will automatically assign it a value (1 for the first row).

Result After Insertion

Inserting Multiple Rows at Once

Instead of running a separate INSERT INTO for each row, multiple rows can be inserted in one statement by separating each set of values with a comma.

INSERT INTO Students (StudentName, Age, City, JoinDate)
VALUES
    ('Priya Mehta',  22, 'Mumbai',  '2024-02-05'),
    ('Arjun Nair',   19, 'Chennai', '2024-02-20'),
    ('Sneha Kapoor', 21, 'Pune',    '2024-03-01'),
    ('Rohit Das',    23, 'Kolkata', '2024-03-15');

Result After Insertion

Inserting Data Into Specific Columns Only

It is not always necessary to insert values into every column. Columns that allow NULL values can be skipped, and they will be stored as NULL (empty/unknown).

INSERT INTO Students (StudentName, Age)
VALUES ('Meena Joshi', 24);

Result of This Row

City and JoinDate are stored as NULL because no value was provided for them.

Important Rules for INSERT INTO

• Text values (strings and dates) must be wrapped in single quotes — for example, 'Ravi' or '2024-01-10'.
• Numeric values are written without quotes — for example, 20 or 99.99.
• The number of values in VALUES must match the number of columns listed in the column list.
• Columns marked as NOT NULL must always receive a value — they cannot be skipped.
• Columns with AUTO_INCREMENT should not be included in the column list unless a specific value needs to be assigned.

What Happens If a Rule Is Broken?

Verifying Inserted Data

After inserting data, use the SELECT statement to confirm that the records were added correctly:

SELECT * FROM Students;

This retrieves all rows and columns from the Students table to verify the inserted data.

Key Points to Remember

• Always list column names in INSERT INTO for clarity and safety.
• Multiple rows can be inserted in a single statement — this is more efficient than multiple separate inserts.
• String and date values require single quotes; numbers do not.
• NULL will be stored in any column that is not given a value (as long as that column allows NULL).

Summary

The INSERT INTO statement is how data gets added to a table. Rows can be inserted one at a time or in bulk. Always ensure the values being inserted match the column's data type and constraints. After inserting data, use SELECT to verify the results.