INSERT INTO — Add One Row

An empty table is useless until you put data in it. INSERT is how every row in every database got there — each player, each match, each ball.

Definition

INSERT INTO is a DML statement that adds one or more new rows to a table. You specify the target table, optionally the list of columns you're filling, and the values for those columns — in the same order.

• New rows — INSERT never changes existing rows; it always adds.
• Column list (optional) — tells SQL which column each value goes into.
• Same order — the 1st value goes into the 1st listed column, the 2nd into the 2nd, and so on.

Two ways to add data

Syntax

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

Rule: Match the number of columns and values. 5 columns listed → exactly 5 values. Each value must also match its column's data type.

Writing values:

• Text and dates go in single quotes: 'Virat Kohli', '2008-04-18'.
• Numbers have no quotes: 750.
• Missing / unknown → NULL (no quotes).

Example — add one row to an empty table

Before: players is empty (0 rows).

INSERT INTO players (id, name, team, country, runs)
VALUES (1, 'Virat Kohli', 'RCB', 'India', 750);

SELECT * FROM players;

After — 1 new row added:

Step by step: 1️⃣ INSERT INTO players → 2️⃣ list the columns → 3️⃣ VALUES in the same order. One INSERT with one value list adds one new row.

Example — leaving columns out

If you list only some columns, the others get their DEFAULT value, or NULL if they have no default:

INSERT INTO players (id, name, team)
VALUES (7, 'Shubman Gill', 'GT');

SELECT * FROM players WHERE id = 7;

You cannot leave out a NOT NULL column that has no default (id and name here) — SQL Server raises "Cannot insert the value NULL into column 'name'…".

Why you should always write the column list

-- Works, but fragile
INSERT INTO players VALUES (8, 'Rinku Singh', 'KKR', 'India', 120);

-- Clear and safe
INSERT INTO players (id, name, team, country, runs)
VALUES (8, 'Rinku Singh', 'KKR', 'India', 120);

Without a column list, the statement depends on the table's current column order. If someone later adds a column with ALTER TABLE, the short version breaks (wrong number of values) or, worse, puts values into the wrong columns.

Common mistakes

• ❌ VALUES (1, 'Virat Kohli', 'RCB') for 5 columns → "There are fewer columns in the INSERT statement than values specified…" ✅ Same count on both sides.
• ❌ VALUES (1, Virat Kohli, ...) ✅ Text needs quotes: 'Virat Kohli'.
• ❌ VALUES (1, "Virat Kohli", ...) ✅ SQL Server text uses single quotes; double quotes mean an identifier.
• ❌ Values in a different order than the columns: (id, name) VALUES ('Virat', 1). ✅ Same order.
• ❌ Inserting an id that already exists → PRIMARY KEY violation. ✅ Use a new id (or let IDENTITY generate it).
• ❌ A name like 'D'Souza' breaks the string. ✅ Double the quote: 'D''Souza'.

SQL Server vs MySQL / PostgreSQL

The INSERT INTO … VALUES syntax is the same in all three. Differences appear for auto-generated ids: SQL Server IDENTITY (don't list the column), MySQL AUTO_INCREMENT, PostgreSQL GENERATED … AS IDENTITY / SERIAL.

Interview tip

Q: What happens to columns you don't mention in an INSERT? A: They receive their DEFAULT value if one is defined, otherwise NULL. If such a column is NOT NULL without a default, the INSERT fails.