INSERT - Adding Data

Inserting Single Rows

The INSERT INTO statement is used to add new rows of data into a table. You specify the table name, the columns you want to fill, and the values for those columns.

You can either specify all columns or only the ones you want to fill — columns with DEFAULT values or AUTO_INCREMENT will be handled automatically.

-- Insert a single row specifying all columns
INSERT INTO users (username, email, age, bio, join_date, is_active)
VALUES ('alice', 'alice@example.com', 28, 'Software developer', '2026-01-15', TRUE);

-- Insert a row using only required columns
-- (id auto-increments, join_date and is_active use defaults)
INSERT INTO users (username, email, age)
VALUES ('bob', 'bob@example.com', 34);

-- Verify the inserted data
SELECT * FROM users;

Inserting Multiple Rows & Default Values

You can insert multiple rows in a single INSERT statement by separating each set of values with a comma. This is much more efficient than running separate INSERT statements for each row.

When a column has a DEFAULT value or AUTO_INCREMENT, you can omit it from the INSERT statement and the database will fill it in automatically.

-- Insert multiple rows at once
INSERT INTO users (username, email, age, bio)
VALUES 
    ('charlie', 'charlie@example.com', 22, 'Student'),
    ('diana', 'diana@example.com', 31, 'Data analyst'),
    ('eve', 'eve@example.com', 27, 'UX designer');

-- Using DEFAULT keyword explicitly
INSERT INTO users (username, email, age, join_date)
VALUES ('frank', 'frank@example.com', 40, DEFAULT);

-- Check all rows
SELECT * FROM users;

Handling Auto-Increment & Retrieving Inserted IDs

When a column is set to AUTO_INCREMENT, the database automatically assigns the next sequential integer to each new row. You do not need to specify a value for this column.

After inserting a row, you may need to know the ID that was automatically assigned. SQL provides functions to retrieve the last inserted ID.

• AUTO_INCREMENT starts at 1 by default and increases by 1 for each new row
• If a row is deleted, its ID is not reused — the sequence continues forward
• You can reset the auto-increment counter, but this is rarely needed in practice

-- The id column auto-increments — no need to specify it
INSERT INTO users (username, email, age)
VALUES ('grace', 'grace@example.com', 29);

-- Get the last auto-generated ID (MySQL)
SELECT LAST_INSERT_ID();

-- In PostgreSQL, use RETURNING
-- INSERT INTO users (username, email, age)
-- VALUES ('grace', 'grace@example.com', 29)
-- RETURNING id;