Basic Syntax

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

Insert a Single Row

-- Insert with explicit column names (recommended)
INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Alice', 'Johnson', 'alice@email.com', 'New York');

-- Insert with all columns (order matters!)
INSERT INTO customers
VALUES (1, 'Bob', 'Smith', 'bob@email.com', 'Los Angeles', 'CA', '2024-01-15', 1);

Column Order Matters

Insert Multiple Rows

-- Efficient: Insert multiple rows in one statement
INSERT INTO customers (first_name, last_name, email, city)
VALUES
    ('Bob', 'Smith', 'bob@email.com', 'Los Angeles'),
    ('Carol', 'Williams', 'carol@email.com', 'Chicago'),
    ('Dan', 'Brown', 'dan@email.com', 'Houston'),
    ('Eve', 'Davis', 'eve@email.com', 'Seattle'),
    ('Frank', 'Miller', 'frank@email.com', 'Denver');

Performance Comparison

INSERT INTO ... SELECT

-- Copy customers from New York to archive
INSERT INTO archive_customers (first_name, last_name, email)
SELECT first_name, last_name, email
FROM customers
WHERE city = 'New York';

-- Copy with transformations
INSERT INTO customer_summary (full_name, email_domain)
SELECT 
    first_name || ' ' || last_name AS full_name,
    SUBSTR(email, INSTR(email, '@') + 1) AS email_domain
FROM customers
WHERE is_active = TRUE;

INSERT with Expressions

-- Use expressions to compute values
INSERT INTO products (name, price, discounted_price, stock, category)
VALUES (
    'Laptop',
    999.99,
    999.99 * 0.9,           -- 10% discount
    50,
    'Electronics'
);

-- Use subqueries
INSERT INTO product_stats (category, avg_price, total_stock)
SELECT 
    category,
    AVG(price),
    SUM(stock)
FROM products
GROUP BY category;

INSERT with NULL Values

-- Explicit NULL
INSERT INTO customers (first_name, last_name, email, phone)
VALUES ('Alice', 'Johnson', 'alice@email.com', NULL);

-- Implicit NULL (omit column)
INSERT INTO customers (first_name, last_name, email)
VALUES ('Bob', 'Smith', 'bob@email.com');
-- phone will be NULL (if no DEFAULT defined)

-- Check for NULL after insert
SELECT * FROM customers WHERE phone IS NULL;

INSERT with DEFAULT Values

-- Let defaults apply automatically
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 5, 2);
-- order_date, status, total use defaults

-- Explicitly use DEFAULT
INSERT INTO orders (customer_id, product_id, quantity, status)
VALUES (1, 5, 2, DEFAULT);
-- status will use its DEFAULT value

-- Mix explicit and default values
INSERT INTO products (name, price, stock, category)
VALUES ('Mouse', 29.99, DEFAULT, 'Electronics');

INSERT with Constraints

-- Example with multiple constraints
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    salary DECIMAL(10,2) CHECK (salary >= 30000),
    department TEXT DEFAULT 'General',
    hire_date DATE DEFAULT CURRENT_DATE
);

-- This will succeed
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Alice', 'Johnson', 'alice@company.com', 75000);

-- This will FAIL (duplicate email)
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Bob', 'Smith', 'alice@company.com', 65000);
-- Error: UNIQUE constraint failed: employees.email

-- This will FAIL (salary too low)
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Carol', 'Williams', 'carol@company.com', 25000);
-- Error: CHECK constraint failed: employees.salary

INSERT with Transactions

-- Use transactions for related inserts
BEGIN TRANSACTION;

INSERT INTO customers (first_name, last_name, email)
VALUES ('Alice', 'Johnson', 'alice@email.com');

INSERT INTO orders (customer_id, product_id, quantity, total)
VALUES (LAST_INSERT_ID(), 1, 2, 59.98);

COMMIT;

-- If any insert fails, ROLLBACK to undo all changes

Common Errors

-- ERROR: Column count doesn't match value count
INSERT INTO customers (first_name, last_name)
VALUES ('Alice', 'Johnson', 'alice@email.com');
-- 2 columns, 3 values

-- ERROR: Data type mismatch
INSERT INTO customers (age)
VALUES ('twenty-five');  -- age is INTEGER, not TEXT

-- ERROR: NOT NULL violation
INSERT INTO customers (email)
VALUES ('alice@email.com');  -- first_name and last_name are NOT NULL

-- ERROR: UNIQUE violation
INSERT INTO customers (email)
VALUES ('existing@email.com');  -- email already exists

Database Specific Features

-- MySQL: INSERT IGNORE (skip duplicates)
INSERT IGNORE INTO customers (email, name)
VALUES ('alice@email.com', 'Alice');

-- MySQL: INSERT...ON DUPLICATE KEY UPDATE
INSERT INTO inventory (product_id, quantity)
VALUES (1, 10)
ON DUPLICATE KEY UPDATE quantity = quantity + 10;

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

-- SQL Server: INSERT with OUTPUT
INSERT INTO customers (name, email)
OUTPUT INSERTED.id, INSERTED.name
VALUES ('Alice', 'alice@email.com');

Performance Tips

Summary