Using INSERT to Add Data

Earlier lessons read data with SELECT, but real applications create records too: registration stores a user and an administrator writes a new product to products. SQL normally adds rows with INSERT INTO.

The statement has two parts: the first names the target table and receiving columns; VALUES supplies data in the same order. Querying users afterward reveals Emma as a new row.

INSERT INTO users (id, name, email, city)
VALUES (5, 'Emma', '[email protected]', 'Nagoya');

Columns and values must correspond

(name, email, city) maps to ('Emma', '[email protected]', 'Nagoya'). If Nagoya and the email are reversed, the database follows position rather than guessing intent. A type conflict may reject the statement; compatible types may allow incorrect data to be stored.

Why is id sometimes omitted?

Real schemas often generate primary-key IDs automatically. MySQL, PostgreSQL, and SQL Server implement this differently, but the shared idea is that the application supplies business data and the database assigns a unique number.

You can provide only some columns

A column that allows NULL or has a DEFAULT may be omitted. With only name and email, city may become NULL or use its default. A NOT NULL column without a default causes failure. Valid insertion depends on both SQL syntax and table rules.

Insert several rows at once

Many databases accept several VALUES groups. This is useful for test data and small batches and is often clearer than many separate INSERT statements. Once one-row correspondence is clear, multiple rows repeat the same structure.

INSERT INTO users (name, email, city)
VALUES
  ('Emma', '[email protected]', 'Nagoya'),
  ('Frank', '[email protected]', 'Tokyo'),
  ('Grace', '[email protected]', 'Osaka');

Strings, numbers, and dates

Text normally uses single quotes; numbers do not. In orders, user_id, product_id, and quantity are numbers, while order_date can use a clear YYYY-MM-DD string. Date details vary among products.

Verify after writing

Query immediately after INSERT. This catches wrong column order, incorrect values, or a failed statement. Unlike SELECT, which only produces a result set, INSERT changes the table, so confirm the database, table, and data before writing to production.

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

CREATE TABLE users (id INTEGER, name VARCHAR(100), email VARCHAR(200), city VARCHAR(100));
CREATE TABLE products (id INTEGER, name VARCHAR(100), price DECIMAL(10,2), category VARCHAR(50));