🎓 A1Learner
🔍

SQL Insert

INSERT INTO is the SQL command that adds new rows to a table. The statement names the table, the columns, and the values for each new row.

Add one row

The column list names four columns. The VALUES list gives one value for each column, in the same order.

INSERT INTO customers (customer_id, name, city, country)
VALUES (9, 'Ivy Chen', 'Seattle', 'USA');
SELECT customer_id, name, city, country
FROM customers
WHERE customer_id = 9;

Add several rows at once

One statement can carry several rows. Each row gets its own set of parentheses after VALUES.

INSERT INTO products (product_id, name, category, price, stock)
VALUES (9, 'Tea Towel', 'Kitchen', 6.25, 60),
(10, 'Cutting Board', 'Kitchen', 14.50, 25);
SELECT product_id, name, category, price, stock
FROM products
WHERE product_id >= 9
ORDER BY product_id;

Leave a column out

The column list here skips city. The new row stores NULL for city, and the closing SELECT shows it.

INSERT INTO customers (customer_id, name, country)
VALUES (10, 'Jon Park', 'Canada');
SELECT customer_id, name, city, country
FROM customers
WHERE customer_id = 10;

Example

INSERT INTO products (product_id, name, category, price, stock)
VALUES (9, 'Tea Towel', 'Kitchen', 6.25, 60);
SELECT name, price
FROM products
WHERE category = 'Kitchen'
ORDER BY name;

Key points

  • INSERT INTO: the command that adds new rows to a table
  • Column list: the column names in parentheses after the table name
  • VALUES: the keyword that introduces the values for a new row
  • The values follow the same order as the column list.
  • One INSERT statement can add several rows, with one set of parentheses per row.
  • A column left out of the list holds NULL in the new row.
  • A closing SELECT proves the new rows are stored.

In this example, the INSERT statement adds one row to products. The new row names a Tea Towel in the Kitchen category. The SELECT then lists every Kitchen product by name. The Tea Towel row sits between the two older rows, so the insert is proven.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does the column list in an INSERT statement do?