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

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

Batch multiple rows using the same syntax

INSERTINTO 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:

INSERTINTO 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.

INSERTINTO 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

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

Example 2: Create starter users

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

Example 3: Copy completed tasks into an archive table

INSERTINTO 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:

INSERTINTO users (username, email)
VALUES ('alex');

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

Correct approach:

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

Mistake 2: Missing required columns

What you might do:

INSERTINTO users (username)
VALUES ('alex');

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

Correct approach:

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

Mistake 3: Duplicate primary keys

What you might do:

INSERTINTO 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