INSERT

Inserting a row

INSERT INTO customers (name, city, country, joined)
VALUES ('Zara Ali', 'Dubai', 'UAE', '2024-08-01');

SELECT * FROM customers ORDER BY customer_id DESC LIMIT 3;

• List the columns you're filling, then the values in the same order.
• customer_id isn't listed: an INTEGER PRIMARY KEY takes the next free number by itself. Run the example again and you'll get a second Zara with the next id.

Several rows at once

INSERT INTO products (name, category, price, stock) VALUES
  ('Mouse Pad',   'Accessories', 9.99,  200),
  ('HDMI Cable',  'Accessories', 12.50, 150),
  ('Monitor Arm', 'Displays',    64.00, 30);

SELECT product_id, name, price, stock
FROM products
WHERE price < 15 OR name = 'Monitor Arm';

One statement with many rows is much faster than many one-row statements.

Columns you leave out

A column you don't mention gets its default value, which is NULL unless the table defines a different default:

INSERT INTO customers (name) VALUES ('Walk-in customer');

SELECT * FROM customers WHERE city IS NULL;

Always name the columns

This also works:

INSERT INTO customers VALUES (50, 'Kai Silva', 'Lima', 'Peru', '2024-08-02');

But only while the table has exactly those columns, in exactly that order. Add a column next year and every statement written this way breaks. Naming the columns is safer.

When the database says no

Constraints protect the data. customer_id must be unique:

INSERT INTO customers (customer_id, name) VALUES (1, 'Duplicate');

And name is declared NOT NULL:

INSERT INTO customers (city, country) VALUES ('Pune', 'India');

Both fail and nothing is inserted. That's the database doing its job.

Copying rows: INSERT ... SELECT

Instead of VALUES, an INSERT can take the rows a query returns. This copies cancelled orders into an archive table:

CREATE TABLE IF NOT EXISTS order_archive (order_id INTEGER, order_date TEXT, status TEXT);

INSERT INTO order_archive (order_id, order_date, status)
SELECT order_id, order_date, status FROM orders WHERE status = 'cancelled';

SELECT * FROM order_archive;

Run it twice and order 105 is archived twice: nothing stops duplicates unless a constraint does, such as a PRIMARY KEY on order_id.

Getting the new row back: RETURNING

RETURNING hands back the row that was just inserted, including the id the database chose. It works in SQLite and PostgreSQL; SQL Server's version is called OUTPUT.

INSERT INTO customers (name, city, country, joined)
VALUES ('Diego Alves', 'Lisbon', 'Portugal', DATE('now'))
RETURNING customer_id, name, joined;