How to Insert Data in SQL

What you’ll build or solve

You’ll add one or more rows to an existing table.

When this approach works best

Inserting data works best when you:

• Add new users, orders, or products
• Seed a database with starter data
• Store results from another query in a table

For example, you might insert a new user into users, add a purchase to orders, or copy filtered records into a reporting table.

This is a bad idea if you need to change existing rows. Use UPDATE when you want to modify records that are already there.

Prerequisites

• Access to the database
• Permission to insert into the target table
• Basic SQL knowledge

Step-by-step instructions

Step 1: Insert rows with VALUES

Use INSERT INTO ... VALUES when you already know the values you want to store.

Single row

INSERT INTO users (username, email)
VALUES ('alex', 'alex@example.com');

Batch multiple rows using the same syntax

INSERT INTO users (username, email)
VALUES
  ('mia', 'mia@example.com'),
  ('leo', 'leo@example.com'),
  ('noah', 'noah@example.com');

If your table has an auto-incrementing ID, omit that column:

INSERT INTO users (username, email)
VALUES ('sam', 'sam@example.com');

What to look for

• Column names must exist in the table
• Value order must match the column list
• Data types must match column definitions
• Required columns without defaults must be included

Step 2: Insert data from query results

Use INSERT INTO ... SELECT when you want to insert rows produced by a query.

INSERT INTO active_users (id, username, email)
SELECT id, username, email
FROM users
WHERE is_active = TRUE;

This pattern is common for archiving, reporting, or staging data.

What to look for

• The number and order of selected columns must match the target table
• Data types must be compatible
• Filters in the SELECT control which rows get inserted

Examples you can copy

Example 1: Add a new product

INSERT INTO products (name, price, stock)
VALUES ('Laptop Stand', 29.99, 100);

Example 2: Create starter users

INSERT INTO users (username, email)
VALUES
  ('jordan', 'jordan@example.com'),
  ('sasha', 'sasha@example.com');

Example 3: Copy completed tasks into an archive table

INSERT INTO completed_tasks (id, title, completed_at)
SELECT id, title, completed_at
FROM tasks
WHERE status = 'done';

Common mistakes and how to fix them

Mistake 1: Mismatched column counts

What you might do:

INSERT INTO users (username, email)
VALUES ('alex');

Why it breaks: The number of values does not match the number of columns.

Correct approach:

INSERT INTO users (username, email)
VALUES ('alex', 'alex@example.com');

Mistake 2: Missing required columns

What you might do:

INSERT INTO users (username)
VALUES ('alex');

Why it breaks: If email is NOT NULL and has no default, the insert fails.

Correct approach:

INSERT INTO users (username, email)
VALUES ('alex', 'alex@example.com');

Mistake 3: Duplicate primary keys

What you might do:

INSERT INTO users (id, username, email)
VALUES (1, 'newuser', 'new@example.com');

Why it breaks: If id = 1 already exists, the database blocks the insert.

Correct approach: Let the database assign IDs when possible, or insert a value that does not conflict.

Troubleshooting

• If you see “permission denied,” confirm you have INSERT privileges.
• If you see “column does not exist,” check spelling and casing.
• If you see “duplicate key value violates unique constraint,” inspect primary and unique keys.
• If you see a data type error, confirm your values match the column types.

Quick recap

• Use INSERT INTO ... VALUES to insert known values
• Batch inserts by listing multiple value tuples
• Use INSERT INTO ... SELECT to insert query results
• Match column counts, order, and types
• Include required fields and avoid key conflicts