INSERT - Adding Data

The Two Forms of INSERT, and Why Only One Is Safe

INSERT adds rows to a table. It has two forms. The first names the columns you are filling and then supplies matching values. The second skips the column list entirely and relies on you supplying a value for every column, in the exact order they were declared.

The short form looks tempting because there is less to type. Do not use it in anything you intend to keep. It silently depends on the physical order of columns in the table, and column order changes — the day someone runs ALTER TABLE students ADD COLUMN city, every short-form INSERT in your codebase either errors or, worse, starts writing values into the wrong columns. Naming the columns costs one line and makes the statement self-documenting and immune to that whole class of bug.

The values themselves follow simple rules. Text goes in single quotes. Numbers do not. Dates are written as quoted text in 'YYYY-MM-DD' form, which is the one format every SQL database understands without ambiguity — never '02-08-2026', because no engine can tell whether that means 2 August or 8 February. Columns that are auto-generated or have a DEFAULT can simply be left out.

-- The safe form: name the columns
INSERT INTO students (name, email, branch, marks, joined_on)
VALUES ('Ananya Sharma', 'ananya@example.com', 'CSE', 88, '2026-07-01');

-- id is AUTO_INCREMENT and is_active has a DEFAULT, so both are omitted
INSERT INTO students (name, email, branch)
VALUES ('Rahul Verma', 'rahul@example.com', 'ECE');

-- The short form: fragile, avoid it
-- INSERT INTO students VALUES (NULL, 'Meera Nair', 'meera@example.com', 'CSE', 91, ...);

-- A name containing an apostrophe: double the quote to escape it
INSERT INTO students (name, email, branch)
VALUES ('Rohan D''Souza', 'rohan@example.com', 'IT');

SELECT * FROM students;

Inserting Many Rows in One Statement

You can list several rows in one INSERT by separating the value groups with commas. This is not just tidier — it is dramatically faster. Each separate statement carries its own round trip to the server and, by default, its own commit to disk. Loading a thousand rows one statement at a time can take minutes where a handful of batched statements take a second.

There is a second, less obvious property. A multi-row INSERT is a single statement, so it either fully succeeds or fully fails. If the fourth row of five violates a UNIQUE constraint, none of the five are inserted. That is usually what you want when loading a batch of related data, but it does mean one bad row can reject a whole file, so validate before loading.

Do not push this too far. A statement with a hundred thousand value groups can exceed the server's maximum packet size and will be rejected outright. Batches of a few hundred to a few thousand rows are the practical sweet spot.

-- Five students in one statement
INSERT INTO students (name, email, branch, marks) VALUES
    ('Meera Nair',    'meera@example.com',   'CSE', 91),
    ('Karan Mehta',   'karan@example.com',   'ECE', 67),
    ('Priya Iyer',    'priya@example.com',   'CSE', 79),
    ('Arjun Singh',   'arjun@example.com',   'MECH', 55),
    ('Fatima Khan',   'fatima@example.com',  'IT',  84);

-- Copying rows from one table into another: INSERT ... SELECT
-- (no VALUES keyword at all)
CREATE TABLE toppers (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR(100),
    branch VARCHAR(20),
    marks  INT
);

INSERT INTO toppers (name, branch, marks)
SELECT name, branch, marks
FROM students
WHERE marks >= 85;

Missing Values: Omitted, DEFAULT, NULL and Empty Are Four Different Things

This is where beginners lose the most time, so it is worth being precise. There are four distinct ways a column can end up "without a real value", and they behave differently.

If you omit the column from the insert, the database uses its DEFAULT if one is declared, and NULL if not — and errors if the column is NOT NULL with no default. Writing DEFAULT as the value does the same thing explicitly. Writing NULL as the value stores an actual NULL and ignores the default, which surprises people. And writing '' stores an empty string, which is a real value that is not NULL at all.

The practical consequence arrives one lesson later, when you filter. A row with phone = NULL is not found by WHERE phone = '', and a row with phone = '' is not found by WHERE phone IS NULL. If half your rows use one convention and half the other, no single query finds all the students without a phone number. Pick one — NULL for "unknown" is the conventional choice — and apply it everywhere.

The same trap catches numbers. marks = 0 means the student scored zero. marks = NULL means the exam has not been marked yet. Averaging a column where absences were recorded as 0 gives a genuinely wrong class average, because AVG counts those zeroes while it would have skipped the NULLs.

• Column omitted → the DEFAULT is used, or NULL if there is no default
• DEFAULT written as the value → the same thing, stated explicitly
• NULL written as the value → stores NULL and bypasses the default
• '' written as the value → stores an empty string, which is a value, not a missing value
• 'NULL' in quotes → stores the four-letter word NULL as text; almost always a mistake

CREATE TABLE enquiries (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    phone    VARCHAR(15),
    source   VARCHAR(30) DEFAULT 'website',
    added_on TIMESTAMP   DEFAULT CURRENT_TIMESTAMP
);

-- source omitted -> becomes 'website'
INSERT INTO enquiries (name, phone) VALUES ('Ananya', '9876543210');

-- source given as DEFAULT -> also 'website'
INSERT INTO enquiries (name, phone, source) VALUES ('Rahul', NULL, DEFAULT);

-- source given as NULL -> stored as NULL, the default is NOT applied
INSERT INTO enquiries (name, phone, source) VALUES ('Meera', '', NULL);

-- Now look at what actually happened
SELECT id, name, phone, source FROM enquiries;

-- Rahul has phone NULL; Meera has phone '' (empty string).
-- Neither query below finds both of them:
SELECT name FROM enquiries WHERE phone IS NULL;   -- Rahul only
SELECT name FROM enquiries WHERE phone = '';      -- Meera only

Getting Back the Id You Just Created

When you insert an order and then need to insert its line items, you need the order's generated id — and you did not choose it, the database did. Every product provides a way to ask for it, and once again the spellings differ.

In MySQL, LAST_INSERT_ID() returns the value generated by the most recent insert on your own connection. That last part is the important guarantee: another user inserting at the same moment on a different connection cannot corrupt your answer. It is not "the largest id in the table", and you must never substitute SELECT MAX(id) FROM orders for it — under any real concurrency that returns somebody else's row.

One MySQL detail catches people with batches: after a multi-row insert, LAST_INSERT_ID() returns the id of the first row inserted, not the last. If you need all of them, insert the rows individually or use the row count to work out the range.

-- MySQL
INSERT INTO orders (student_id, total) VALUES (3, 1499.00);
SELECT LAST_INSERT_ID() AS new_order_id;

-- and then use it
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LAST_INSERT_ID(), 7, 2);

-- PostgreSQL: ask the INSERT itself to return the row
-- INSERT INTO orders (student_id, total) VALUES (3, 1499.00)
-- RETURNING id;

-- SQL Server
-- INSERT INTO orders (student_id, total) VALUES (3, 1499.00);
-- SELECT SCOPE_IDENTITY();

-- SQLite
-- SELECT last_insert_rowid();

-- WRONG under concurrency - never do this
-- SELECT MAX(id) FROM orders;

When an INSERT Collides With an Existing Row

Sooner or later you will insert a row whose email already exists, and the UNIQUE constraint will stop you with a duplicate-key error. That error is the constraint doing its job. The question is what your program should do next, and SQL offers three answers.

You can let it fail and handle the error in your code — the right choice when a duplicate genuinely means the user made a mistake and should be told so. You can ask the database to skip the row quietly. Or you can ask it to update the existing row instead, which is called an upsert and is what you want when re-running an import that may contain rows you already loaded.

The syntax for the last two is thoroughly dialect-specific. MySQL uses INSERT IGNORE and ON DUPLICATE KEY UPDATE. PostgreSQL and SQLite use ON CONFLICT. Nothing here is portable, so pick the form for the database you are actually running and keep the difference in mind when reading answers online.

-- MySQL: skip rows that would collide (they are reported as warnings)
INSERT IGNORE INTO students (name, email, branch)
VALUES ('Ananya Sharma', 'ananya@example.com', 'CSE');

-- MySQL upsert: insert, or update the existing row if the email is taken
INSERT INTO students (name, email, branch, marks)
VALUES ('Ananya Sharma', 'ananya@example.com', 'CSE', 93)
ON DUPLICATE KEY UPDATE
    marks  = VALUES(marks),
    branch = VALUES(branch);

-- PostgreSQL and SQLite equivalent
-- INSERT INTO students (name, email, branch, marks)
-- VALUES ('Ananya Sharma', 'ananya@example.com', 'CSE', 93)
-- ON CONFLICT (email) DO UPDATE
--   SET marks = EXCLUDED.marks, branch = EXCLUDED.branch;

-- PostgreSQL and SQLite: skip instead of update
-- INSERT INTO students (name, email, branch)
-- VALUES ('Ananya Sharma', 'ananya@example.com', 'CSE')
-- ON CONFLICT (email) DO NOTHING;