INSERT: Adding Data to Tables

You've learned how to create tables—now it's time to fill them with data! INSERT is the SQL command that adds new rows to your tables. Whether you're adding a single customer, importing thousands of products, or populating a database from scratch, INSERT is your go-to command. In this lesson, you'll learn multiple ways to insert data, how to handle constraints, work with auto-increment columns, and avoid common pitfalls.

📚 What You'll Learn

• How to insert single rows into tables
• How to insert multiple rows at once
• How to specify which columns to populate
• How to work with AUTO_INCREMENT primary keys
• How to insert dates and timestamps
• How to handle DEFAULT values
• How to insert data from other tables
• How to handle constraint violations
• Common INSERT errors and how to fix them

1. Basic INSERT Syntax

Two Common Formats

Format 1: Specify Column Names (Recommended)

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

Format 2: Insert All Columns in Order

INSERT INTO table_name
VALUES (value1, value2, value3, value4, value5);

2. Simple INSERT Examples

Example 1: Insert a Customer

-- Table structure
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a single customer
INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('John', 'Smith', 'john.smith@email.com', '555-0123');

Notice:

• We didn't specify customer_id - it's AUTO_INCREMENT, so MySQL assigns it automatically
• We didn't specify created_at - it has a DEFAULT, so MySQL uses CURRENT_TIMESTAMP
• We only specified the columns we want to set
• Strings are enclosed in single quotes

Example 2: Insert a Product

-- Insert a product with all details
INSERT INTO products (name, sku, price, stock_quantity, is_available)
VALUES ('Wireless Mouse', 'WM-001', 29.99, 150, TRUE);

-- Insert with some columns using defaults
INSERT INTO products (name, sku, price)
VALUES ('USB Keyboard', 'KB-001', 49.99);
-- stock_quantity defaults to 0, is_available defaults to TRUE

3. Inserting Multiple Rows at Once

Instead of running multiple INSERT statements, you can insert several rows in a single command. This is much faster for bulk operations.

Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES 
    (value1a, value2a, value3a),
    (value1b, value2b, value3b),
    (value1c, value2c, value3c);

Example: Insert Multiple Customers

INSERT INTO customers (first_name, last_name, email, phone)
VALUES 
    ('Alice', 'Johnson', 'alice.j@email.com', '555-0124'),
    ('Bob', 'Williams', 'bob.w@email.com', '555-0125'),
    ('Carol', 'Davis', 'carol.d@email.com', '555-0126'),
    ('David', 'Miller', 'david.m@email.com', NULL),
    ('Eve', 'Wilson', 'eve.w@email.com', '555-0128');

-- This inserts 5 customers with one command!

4. Working with AUTO_INCREMENT Columns

AUTO_INCREMENT columns automatically generate unique IDs. You typically don't insert values into them—MySQL handles it for you.

Let MySQL Handle It

-- ✅ Recommended: Don't specify the AUTO_INCREMENT column
INSERT INTO customers (first_name, last_name, email)
VALUES ('Frank', 'Brown', 'frank.b@email.com');
-- MySQL automatically assigns customer_id = 1 (or next available ID)

-- Get the ID that was just assigned
SELECT LAST_INSERT_ID();  -- Returns the ID of the last inserted row

Explicitly Setting AUTO_INCREMENT (Rare)

-- You CAN specify AUTO_INCREMENT values if needed
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (999, 'Special', 'Customer', 'special@email.com');
-- Future inserts will continue from 1000, 1001, 1002...

5. Inserting Dates and Timestamps

Date Formats

-- DATE: Use 'YYYY-MM-DD' format
INSERT INTO employees (name, hire_date)
VALUES ('John Doe', '2024-03-15');

-- DATETIME: Use 'YYYY-MM-DD HH:MM:SS' format
INSERT INTO events (title, start_time)
VALUES ('Team Meeting', '2024-03-15 14:30:00');

-- TIMESTAMP: Same format as DATETIME
INSERT INTO posts (title, published_at)
VALUES ('My First Post', '2024-03-15 09:00:00');

Using Current Date/Time

-- Use MySQL functions for current date/time
INSERT INTO orders (customer_id, order_date, created_at)
VALUES 
    (1, CURRENT_DATE, CURRENT_TIMESTAMP),
    (2, CURDATE(), NOW()),
    (3, CURRENT_DATE, NOW());

-- All of these work:
-- CURRENT_DATE = CURDATE() = today's date
-- CURRENT_TIMESTAMP = NOW() = current date and time

Date Calculations

-- Insert dates relative to today
INSERT INTO subscriptions (user_id, start_date, end_date)
VALUES 
    (1, CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY)),
    (2, CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 1 YEAR));

6. Working with NULL and DEFAULT Values

Inserting NULL

-- Explicitly insert NULL (for nullable columns)
INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('Jane', 'Doe', 'jane@email.com', NULL);

-- Or just omit the column (defaults to NULL if no DEFAULT specified)
INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Doe', 'jane@email.com');
-- phone will be NULL

Using DEFAULT Values

-- Explicitly use DEFAULT keyword
INSERT INTO products (name, sku, price, stock_quantity)
VALUES ('Mouse Pad', 'MP-001', 9.99, DEFAULT);
-- stock_quantity uses its DEFAULT value (probably 0)

-- Or omit columns with defaults
INSERT INTO products (name, sku, price)
VALUES ('Mouse Pad', 'MP-001', 9.99);
-- stock_quantity and any other DEFAULT columns use their defaults

7. Inserting Data from Other Tables

You can copy data from one table to another using INSERT INTO ... SELECT. This is powerful for data migration, archiving, or creating summary tables.

Basic Syntax

INSERT INTO destination_table (column1, column2, column3)
SELECT column1, column2, column3
FROM source_table
WHERE condition;

Example 1: Copy Active Users to New Table

-- Create backup of active customers
INSERT INTO active_customers (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE account_status = 'active';

Example 2: Create Summary Table

-- Summarize sales by product
INSERT INTO product_sales_summary (product_id, total_quantity, total_revenue)
SELECT 
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(quantity * unit_price) AS total_revenue
FROM order_items
GROUP BY product_id;

Example 3: Populate with Transformations

-- Create full names from separate columns
INSERT INTO customer_contacts (full_name, email, contact_date)
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    email,
    CURRENT_DATE
FROM customers
WHERE email IS NOT NULL;

8. Handling Constraint Violations

Common Errors

1. NOT NULL Violation

INSERT INTO customers (first_name, email)
VALUES ('John', 'john@email.com');
-- ERROR: Column 'last_name' cannot be NULL

Fix: Provide values for all NOT NULL columns without defaults.

2. UNIQUE Violation

INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'john.smith@email.com');
-- ERROR: Duplicate entry 'john.smith@email.com' for key 'email'

Fix: Use a unique email address.

3. FOREIGN KEY Violation

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (999, CURRENT_DATE, 100.00);
-- ERROR: Cannot add or update: foreign key constraint fails
-- (customer_id 999 doesn't exist in customers table)

Fix: Use a valid customer_id that exists in the customers table.

4. CHECK Constraint Violation

INSERT INTO products (name, sku, price, stock_quantity)
VALUES ('Keyboard', 'KB-002', -10.00, 50);
-- ERROR: Check constraint 'chk_positive_price' is violated

Fix: Ensure price is positive (price > 0).

INSERT IGNORE (Skip Errors)

-- INSERT IGNORE: Skip rows that cause errors
INSERT IGNORE INTO customers (first_name, last_name, email)
VALUES 
    ('Alice', 'Smith', 'alice@email.com'),
    ('Bob', 'Jones', 'existing@email.com'),  -- UNIQUE violation - skipped
    ('Carol', 'Davis', 'carol@email.com');
-- Successfully inserts Alice and Carol, skips Bob

ON DUPLICATE KEY UPDATE

-- Update if duplicate key exists, insert if new
INSERT INTO products (sku, name, price, stock_quantity)
VALUES ('WM-001', 'Wireless Mouse', 29.99, 100)
ON DUPLICATE KEY UPDATE 
    price = VALUES(price),
    stock_quantity = stock_quantity + VALUES(stock_quantity);
-- If SKU exists: updates price and adds to stock
-- If SKU is new: inserts the product

9. INSERT Best Practices

10. 🎯 Practice Exercises

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    date_of_birth DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO customers (first_name, last_name, email, phone, date_of_birth)
VALUES 
    ('Sarah', 'Johnson', 'sarah.j@email.com', '555-1001', '1990-05-15'),
    ('Michael', 'Chen', 'michael.c@email.com', NULL, '1985-08-22'),
    ('Emma', 'Davis', 'emma.d@email.com', '555-1003', NULL);

-- Verify the inserts
SELECT * FROM customers;

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_positive_price CHECK (price > 0),
    CONSTRAINT chk_valid_stock CHECK (stock_quantity >= 0)
);

INSERT INTO products (name, sku, price, stock_quantity, is_featured)
VALUES 
    ('Laptop Stand', 'LS-100', 45.99, 75, FALSE),
    ('USB-C Hub', 'HUB-200', 29.99, 50, TRUE),
    ('Desk Mat', 'MAT-300', 19.99, DEFAULT, FALSE);

-- Alternative for Desk Mat (omit columns with defaults)
-- INSERT INTO products (name, sku, price)
-- VALUES ('Desk Mat', 'MAT-300', 19.99);

-- Verify inserts
SELECT * FROM products;

-- What happens with constraint violations?
INSERT INTO products (name, sku, price, stock_quantity)
VALUES ('Bad Product', 'BAD-001', -10.00, 50);
-- ERROR: Check constraint 'chk_positive_price' violated

INSERT INTO products (name, sku, price, stock_quantity)
VALUES ('Duplicate', 'LS-100', 29.99, 10);
-- ERROR: Duplicate entry 'LS-100' for key 'sku'

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    CONSTRAINT chk_positive_quantity CHECK (quantity > 0)
);

-- Step 1: Insert the order
INSERT INTO orders (customer_id, total_amount)
VALUES (1, 131.96);

-- Get the order_id that was just created
SELECT LAST_INSERT_ID();  -- Let's say it returns 1

-- Step 2: Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES 
    (1, 1, 2, 45.99),  -- 2 x Laptop Stand
    (1, 3, 2, 19.99);  -- 2 x Desk Mat

-- Verify the order
SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status,
    COUNT(oi.order_item_id) AS item_count
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 1
GROUP BY o.order_id;

-- View order details
SELECT 
    oi.order_item_id,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id = 1;

CREATE TABLE legacy_sales (
    sale_id INT,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    product_name VARCHAR(255),
    sale_date DATE,
    quantity INT,
    unit_price DECIMAL(10, 2)
);

-- Sample data
INSERT INTO legacy_sales VALUES
(1, 'John Smith', 'john@email.com', 'Laptop', '2024-01-15', 1, 999.99),
(2, 'John Smith', 'john@email.com', 'Mouse', '2024-01-15', 2, 29.99),
(3, 'Jane Doe', 'jane@email.com', 'Keyboard', '2024-01-16', 1, 79.99),
(4, 'Jane Doe', 'jane@email.com', 'Monitor', '2024-01-16', 2, 299.99);

-- Step 1: Extract unique customers
INSERT INTO customers (first_name, last_name, email)
SELECT 
    SUBSTRING_INDEX(customer_name, ' ', 1) AS first_name,
    SUBSTRING_INDEX(customer_name, ' ', -1) AS last_name,
    customer_email
FROM legacy_sales
GROUP BY customer_email;

-- Step 2: Extract unique products (with assumed SKU and stock)
INSERT INTO products (name, sku, price)
SELECT DISTINCT
    product_name,
    CONCAT('LEGACY-', LEFT(product_name, 3), '-', FLOOR(RAND() * 1000)) AS sku,
    MAX(unit_price) AS price  -- Use highest price found
FROM legacy_sales
GROUP BY product_name;

-- Step 3: Create orders (grouped by customer and date)
INSERT INTO orders (customer_id, order_date, total_amount)
SELECT 
    c.customer_id,
    ls.sale_date,
    SUM(ls.quantity * ls.unit_price) AS total_amount
FROM legacy_sales ls
JOIN customers c ON ls.customer_email = c.email
GROUP BY c.customer_id, ls.sale_date;

-- Step 4: Create order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 
    o.order_id,
    p.product_id,
    ls.quantity,
    ls.unit_price
FROM legacy_sales ls
JOIN customers c ON ls.customer_email = c.email
JOIN orders o ON o.customer_id = c.customer_id AND o.order_date = ls.sale_date
JOIN products p ON p.name = ls.product_name;

-- Verify the migration
SELECT 'Customers' AS table_name, COUNT(*) AS count FROM customers
UNION ALL
SELECT 'Products', COUNT(*) FROM products
UNION ALL
SELECT 'Orders', COUNT(*) FROM orders
UNION ALL
SELECT 'Order Items', COUNT(*) FROM order_items;

-- View complete order with items
SELECT 
    o.order_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer,
    o.order_date,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_id, oi.order_item_id;

📝 Key Takeaways

• INSERT INTO adds new rows to tables
• Always specify column names for clarity and safety
• Use batch inserts for better performance (multiple VALUES)
• Let AUTO_INCREMENT handle primary key assignment
• Use LAST_INSERT_ID() to get auto-generated IDs
• Dates use 'YYYY-MM-DD' format
• Use CURRENT_DATE and NOW() for current timestamps
• NULL represents missing data (different from empty string)
• Omitted columns use their DEFAULT values
• INSERT INTO ... SELECT copies data from other tables
• Constraint violations cause INSERT to fail
• Use INSERT IGNORE to skip errors (use carefully)
• Foreign keys must reference existing values
• Always insert parent records before children
• Validate data before inserting into production