SQL — Insert Into

Basic INSERT

INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

Multiple rows

INSERT INTO users (name, email, age)
VALUES
  ('Alice', 'alice@example.com', 30),
  ('Bob', 'bob@example.com', 25),
  ('Charlie', 'charlie@example.com', 35);

INSERT with SELECT

INSERT INTO backup_users (name, email)
SELECT name, email
FROM users
WHERE age > 30;

Default values

CREATE TABLE orders (
  id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending'
);

INSERT INTO orders (id) VALUES (1);
-- status is 'pending'

INSERT with NULL

INSERT INTO users (name, email, phone)
VALUES ('Alice', 'alice@example.com', NULL);

ON CONFLICT (UPSERT)

-- PostgreSQL
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name;

-- MySQL
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Returning inserted data

-- PostgreSQL
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, name;

Mini Practice

Write SQL code that:

1. Inserts a single row
2. Inserts multiple rows
3. Uses ON CONFLICT for upsert
4. Inserts with default values

Up Next

In the next lesson, you'll learn about Update — modifying existing data.