INSERT Statement

Learn how to add new data to tables using the INSERT statement.

14 min read
Beginner

Introduction to INSERT

You've learned to SELECT data, filter it, sort it, and join tables. But where does the data come from in the first place?

The INSERT statement is how you add new rows to a table. Every row in your database was inserted using INSERT (or loaded from files, which uses INSERT behind the scenes).

A SQL command that adds one or more new rows to a table. You specify which table to insert into, which columns to fill, and what values to use.

Why INSERT is important:

  • Populate new tables with data
  • Add user registrations, orders, products, etc.
  • Migrate data between systems
  • Create test data for development

Let's work with a products table:

products (empty initially)
id
product_name
category
price
stock
(empty)

Basic INSERT Syntax

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Let's insert our first product:

sql
-- Insert a single product
INSERT INTO products (id, product_name, category, price, stock)
VALUES (1, 'Laptop', 'Electronics', 999.99, 45);

Success! Let's verify:

sql
SELECT * FROM products;

What happened:

  1. INSERT INTO products - specifies which table
  2. (id, product_name, ...) - lists the columns we're providing values for
  3. VALUES (1, 'Laptop', ...) - provides values in the same order

Important: String values must be in quotes ('Laptop'), numbers don't need quotes.

INSERT with Specific Columns

You don't have to insert values for all columns - only the ones you specify:

sql
-- Insert only some columns (others will be NULL or DEFAULT)
INSERT INTO products (id, product_name, price)
VALUES (2, 'Mouse', 29.99);
sql
SELECT * FROM products WHERE id = 2;

The category and stock columns we didn't specify are set to NULL (or their default value if defined).

Columns with DEFAULT values:

employees
Column
Type
Default
idINTAUTO_INCREMENT
nameVARCHAR(100)-
statusVARCHAR(20)'active'
created_atTIMESTAMPCURRENT_TIMESTAMP
sql
-- id and created_at will use defaults
INSERT INTO employees (name, status)
VALUES ('Alice Smith', 'active');

INSERT Multiple Rows at Once

Instead of running INSERT multiple times, you can insert many rows in one statement:

sql
-- Insert multiple products at once
INSERT INTO products (id, product_name, category, price, stock)
VALUES
  (3, 'Desk Chair', 'Furniture', 199.99, 30),
  (4, 'Monitor', 'Electronics', 349.99, 60),
  (5, 'Keyboard', 'Electronics', 79.99, 95);
sql
SELECT * FROM products;

3 rows inserted with one statement! This is much faster than running INSERT three times.

Benefits of multi-row INSERT:

  • Faster performance (one network round-trip instead of many)
  • Less database overhead
  • Atomic operation (all succeed or all fail together)

INSERT with AUTO_INCREMENT / SERIAL IDs

Most tables have an auto-incrementing ID column. You can omit the ID and let the database generate it:

sql
-- Let the database auto-generate the ID
INSERT INTO products (product_name, category, price, stock)
VALUES ('Desk Lamp', 'Furniture', 39.99, 85);
sql
-- The database assigned id = 6 automatically
SELECT * FROM products WHERE product_name = 'Desk Lamp';

The database automatically assigned ID = 6 (next available number).

Best practice: Don't manually specify auto-increment IDs unless you have a specific reason!

Get the last inserted ID:

In most databases, you can retrieve the auto-generated ID:

  • MySQL: SELECT LAST_INSERT_ID();
  • PostgreSQL: INSERT ... RETURNING id;
  • SQLite: SELECT last_insert_rowid();

INSERT with DEFAULT Values

You can explicitly use DEFAULT for columns with default values:

sql
-- Use DEFAULT keyword for specific columns
INSERT INTO products (id, product_name, category, price, stock)
VALUES (7, 'Bookshelf', DEFAULT, 159.99, DEFAULT);

Or use DEFAULT VALUES to insert a row with all defaults:

sql
-- Insert a row using all default values
INSERT INTO products DEFAULT VALUES;

This only works if all columns have defaults or allow NULL!

INSERT from SELECT - Copying Data

You can insert data from another table (or a query) using INSERT ... SELECT:

products_backup
id
product_name
category
price
stock
(empty - will copy data here)
sql
-- Copy all Electronics products to backup table
INSERT INTO products_backup (id, product_name, category, price, stock)
SELECT id, product_name, category, price, stock
FROM products
WHERE category = 'Electronics';
sql
SELECT * FROM products_backup;

Common uses:

  • Backup data before making changes
  • Migrate data between tables
  • Create summary tables
  • Populate test environments
sql
-- Insert calculated/transformed data
INSERT INTO monthly_sales_summary (month, total_sales, order_count)
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(total) AS total_sales,
  COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

INSERT with Expressions and Functions

You can use expressions and functions in INSERT values:

sql
-- Insert with calculations
INSERT INTO products (product_name, category, price, stock)
VALUES ('Super Mouse', 'Electronics', 29.99 * 1.5, 100 + 20);
sql
-- Insert with string functions
INSERT INTO employees (name, email, created_at)
VALUES
  ('Alice Smith', LOWER('Alice.Smith@company.com'), CURRENT_TIMESTAMP),
  ('Bob Johnson', LOWER('Bob.Johnson@company.com'), CURRENT_TIMESTAMP);
sql
-- Insert with date functions
INSERT INTO orders (customer_id, order_date, delivery_date, total)
VALUES (101, CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY), 500.00);

Common Mistakes to Avoid

Don't make these mistakes:

  1. Wrong number of values: ❌

    • INSERT INTO products (id, name) VALUES (1, 'Laptop', 999);
    • 2 columns but 3 values!
    • Must match: 2 columns = 2 values

  2. Wrong order of values: ❌

    • INSERT INTO products (id, name, price) VALUES ('Laptop', 1, 999);
    • Values must match column order!

  3. Forgetting quotes around strings: ❌

    • VALUES (1, Laptop, Electronics)
    • Should be: VALUES (1, 'Laptop', 'Electronics')

  4. Using quotes around numbers: Usually OK but unnecessary

    • VALUES ('1', 'Laptop', '999.99')
    • Better: VALUES (1, 'Laptop', 999.99)

  5. Inserting NULL into NOT NULL columns: ❌

    • If a column is defined as NOT NULL, you must provide a value
    • Or the column must have a DEFAULT value

  6. Duplicate primary key: ❌

    • INSERT INTO products (id, name) VALUES (1, 'Mouse');
    • If id=1 already exists and id is PRIMARY KEY, this fails!
    • Use auto-increment or check existing IDs

  7. Wrong data type: ❌

    • INSERT INTO products (stock) VALUES ('fifty');
    • stock is INT, can't insert string 'fifty'
    • Use: VALUES (50)

  8. Forgetting to specify columns: ❌ (bad practice)

    • INSERT INTO products VALUES (1, 'Laptop', 'Electronics', 999.99, 45);
    • Works but brittle - if table structure changes, this breaks
    • Always specify columns: INSERT INTO products (id, name, ...) VALUES (...)

Handling Duplicate Keys - ON DUPLICATE KEY UPDATE

What if you try to insert a row with a duplicate primary key?

sql
-- This will fail - id=1 already exists!
INSERT INTO products (id, product_name, price)
VALUES (1, 'New Laptop', 1299.99);

MySQL solution: Use ON DUPLICATE KEY UPDATE to update instead:

sql
-- Insert if new, update if exists
INSERT INTO products (id, product_name, price, stock)
VALUES (1, 'New Laptop', 1299.99, 50)
ON DUPLICATE KEY UPDATE
  product_name = 'New Laptop',
  price = 1299.99,
  stock = 50;

PostgreSQL solution: Use ON CONFLICT:

sql
INSERT INTO products (id, product_name, price, stock)
VALUES (1, 'New Laptop', 1299.99, 50)
ON CONFLICT (id)
DO UPDATE SET
  product_name = EXCLUDED.product_name,
  price = EXCLUDED.price,
  stock = EXCLUDED.stock;

Practical Examples

Example 1: New user registration

sql
INSERT INTO users (username, email, password_hash, created_at)
VALUES
  ('alice99', 'alice@example.com', 'hashed_password_123', NOW()),
  ('bob_smith', 'bob@example.com', 'hashed_password_456', NOW());

Example 2: Placing an order

sql
-- Insert order header
INSERT INTO orders (customer_id, order_date, status, total)
VALUES (101, CURRENT_DATE, 'pending', 1528.97);

-- Insert order line items (using the auto-generated order id)
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
  (LAST_INSERT_ID(), 1, 1, 999.99),
  (LAST_INSERT_ID(), 4, 1, 349.99),
  (LAST_INSERT_ID(), 5, 2, 79.99);

Example 3: Importing data from CSV (conceptual)

sql
-- Bulk insert from staging table
INSERT INTO products (product_name, category, price, stock)
SELECT name, category, price, quantity
FROM products_import
WHERE price > 0 AND quantity >= 0;

Example 4: Creating test data

sql
-- Insert sample customers for testing
INSERT INTO customers (name, email, country, created_at)
VALUES
  ('Test Customer 1', 'test1@example.com', 'USA', NOW()),
  ('Test Customer 2', 'test2@example.com', 'UK', NOW()),
  ('Test Customer 3', 'test3@example.com', 'Canada', NOW());

Key Takeaways

What you learned:

✅ INSERT adds new rows to a table ✅ Syntax: INSERT INTO table (columns) VALUES (values); ✅ Can insert multiple rows with one statement (faster!) ✅ String values need quotes, numbers don't ✅ Can omit columns with DEFAULT values or that allow NULL ✅ Use INSERT ... SELECT to copy data from queries ✅ Let database auto-generate IDs (don't specify them manually) ✅ Values must match the order and number of columns specified ✅ Use ON DUPLICATE KEY UPDATE (MySQL) or ON CONFLICT (PostgreSQL) to handle duplicates

Coming up next: We'll learn more advanced SQL concepts like subqueries, views, and database design!

Practice Exercise: Try these INSERT statements:

  1. Insert a single product with all columns
  2. Insert a product with only name and price (other columns NULL)
  3. Insert 3 products at once (multi-row INSERT)
  4. Use INSERT ... SELECT to copy all Furniture products to a backup table
  5. Insert a product using expressions (e.g., price * 1.10)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.