INSERT — Adding Data to a Table

INSERT — adding rows to a table

To add a new row to a table, you use the INSERT statement. SELECT was about "reading data"; INSERT is the most basic operation for "inserting data." User registration, recording purchases, writing logs — most of an app's "saving something" happens through INSERT.

Naming columns and values (basic form)

The most common form names both the columns and the values: INSERT INTO table_name (col, col, ...) VALUES (val, val, ...);. After the table name, list the columns in parentheses, and after VALUES, list the values in the same order and same count.

In this form, any column not in the list defaults to NULL (or the column's DEFAULT). If you omit an INTEGER PRIMARY KEY column, auto-numbering kicks in and an unused integer is assigned.

-- Add 1 row to book (only title and price)
INSERT INTO book (title, price) VALUES ('Intro to SQL', 1980);

-- Check the result
SELECT * FROM book;

What about omitted columns?

Columns not in the list get the column's DEFAULT (or NULL if there isn't one). For an INTEGER PRIMARY KEY column, auto-numbering kicks in and an unused integer is assigned (the equivalent of MySQL's AUTO_INCREMENT).

Imagine registering a new book in a books database. (Run it correctly and the explanation will appear.)

① Insert 1 row into the book table.

② Specify only two columns, title and price. The values are title='Practical SQL' and price=2480.

③ Finally, run SELECT * FROM book; and confirm id was auto-numbered and published_on is NULL.

Omitting the column list to fill all columns

Another form is the shortcut without a column list, listing values for all columns in order: INSERT INTO table_name VALUES (val, val, ...);. The VALUES keyword comes immediately after the table name.

The trade-off for being shorter: you have to remember the table's column order. And once ALTER TABLE adds a column later, this form breaks because the value count no longer matches — so use it only for limited situations like loading temporary data or throwaway scripts.

-- Add 1 row to task (omitting the column list)
INSERT INTO task VALUES ('Cleaning', 0);

-- Check the result
SELECT * FROM task;

The omitted-columns form breaks under ALTER TABLE

INSERT INTO task VALUES ('Cleaning', 0); works today because the table has 2 columns. If you later run ALTER TABLE task ADD COLUMN due_on TEXT;, every column needs a value (3 of them), and this INSERT breaks with a value-count mismatch error.

For production apps and scripts, prefer the named-columns form (basic form) — when the table definition evolves, you don't have to rewrite your INSERTs. Keep the shortcut form for limited cases like "temporary data with a fixed schema" or "throwaway scripts."

Imagine registering a new task in a todo app.

① INSERT 1 row into the task table with the column list omitted.

② The task column order is title → done. Use title='Shopping' and done=0, in that order.

③ Finally, run SELECT * FROM task; and confirm the row was registered.

Adding multiple rows at once

A single INSERT statement can also add multiple rows at once. Just list multiple `(val, val, ...)` tuples separated by commas after VALUES — each set of parentheses is one row.

There are two reasons to do this. One is less code, and the other is that only one round-trip to the database is needed, which is faster than running an INSERT per row. Bulk loading from CSV files or logs is the standard use case for this form.

-- Add 3 rows to product at once
INSERT INTO product (name, price) VALUES
  ('Apple', 150),
  ('Banana', 100),
  ('Orange', 200);

-- Check the result
SELECT * FROM product;

Imagine seeding an e-commerce site with three initial products.

① INSERT 3 rows at once into the product table.

② Specify two columns, name and price. The three rows are ('Pencil', 80) / ('Eraser', 120) / ('Notebook', 250).

③ Finally, run SELECT * FROM product; and confirm 3 rows with auto-numbered id values.

Where the omitted-columns form breaks — when ALTER TABLE adds a column

The earlier callout said "the omitted-columns form breaks under ALTER TABLE." Seeing it break once gives you a concrete reference to come back to when you're deciding whether to use the shortcut in production.

For the final exercise of this article, create a scratch table `task_v2`, add a column with ALTER TABLE, and try the same omitted-columns INSERT before and after. The INSERT that worked before fails with a "value count mismatch" error the moment a column is added.

Create a scratch task_v2 table and observe the omitted-columns INSERT breaking after an ALTER TABLE.

① Drop any existing same-named table with DROP TABLE IF EXISTS task_v2;, then create a 2-column table with CREATE TABLE task_v2 (title TEXT, done INTEGER);.

② Run INSERT INTO task_v2 VALUES ('Shopping', 0); — this succeeds while the table has 2 columns (the omitted-columns form works).

③ Run ALTER TABLE task_v2 ADD COLUMN due_on TEXT; to add a 3rd column.

④ Run INSERT INTO task_v2 VALUES ('Reading', 0); again. This time only 2 values for 3 columns — it should be rejected with an error.

(This exercise is correct when an error comes back.)