INSERT INTO

Learning Focus

Use this lesson to understand INSERT INTO with practical syntax and examples.

Concept Overview

Definition

INSERT INTO is the SQL statement used to add new rows of data to a table.

Why it matters

Databases are only useful if you can store information. INSERT INTO is the gateway from your application (forms, scripts, APIs) to persistent storage. Without it, a database would remain empty.

Where it fits

INSERT INTO belongs to the Data Manipulation Language (DML) family alongside SELECT, UPDATE, and DELETE. After you design a table with DDL commands such as CREATE TABLE, you use INSERT INTO to populate it.

---

Basic Syntax & Rules

-- Insert a full row (all columns, in order)
INSERT INTO table_name
VALUES (value1, value2, ...);

-- Insert specific columns (recommended)
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Insert multiple rows in one statement
INSERT INTO table_name (col1, col2, ...)
VALUES
  (val1a, val2a, ...),
  (val1b, val2b, ...);

-- Insert data returned from another query
INSERT INTO target_table (col1, col2)
SELECT src_col1, src_col2
FROM source_table
WHERE ... ;

-- Optional modifiers
INSERT IGNORE INTO ...          -- Skip rows that violate constraints
INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col);  -- Upsert pattern

Key parameters & options

Option	What it does	Typical use
IGNORE	Silently skips rows that would cause errors (e.g., duplicate primary key)	Bulk loads where a few bad rows are acceptable
ON DUPLICATE KEY UPDATE	Updates existing row when key conflict occurs	"Upsert" business logic
LOW_PRIORITY / DELAYED*	Lets reads finish before insert (deprecated in MySQL 8.0)	Rarely needed today

Limitations / considerations

• Column list order must match value order.
• String/date values need quotes ('2025-07-05').
• Respect NOT NULL & unique constraints or the insert will fail (unless IGNORE).
• Large bulk inserts are faster inside a single transaction.

---

StepbyStep Examples

Example A - Singlerow insert (simplest)

CREATE TABLE employees (
  emp_id      INT PRIMARY KEY,
  first_name  VARCHAR(30),
  last_name   VARCHAR(30),
  hire_date   DATE,
  salary      DECIMAL(9,2)
);

/* Add one employee */
INSERT INTO employees
VALUES (1, 'Ana', 'Ng', '2025-01-10', 68000.00);

/* Verify */
SELECT * FROM employees;

+--------+------------+-----------+------------+---------+
| emp_id | first_name | last_name | hire_date  | salary  |
+--------+------------+-----------+------------+---------+
|      1 | Ana        | Ng        | 2025-01-10 | 68000.00|
+--------+------------+-----------+------------+---------+

Example B - Multirow insert with explicit column list

INSERT INTO employees (emp_id, first_name, last_name, hire_date, salary)
VALUES
  (2, 'Bilal', 'Singh', '2025-02-01', 72000.00),
  (3, 'Chloe', 'Fernandez', '2025-02-15', 70000.00);

Expected result:

SELECT COUNT(*) FROM employees;
-- 3 rows

Example C - Insert only some columns (others take defaults/NULL)

INSERT INTO employees (emp_id, first_name, last_name)
VALUES (4, 'Dara', 'Jones');

hire_date and salary become NULL (or default) automatically.

Example D - Insert from another table

Suppose you have new_hires staging table:

INSERT INTO employees (emp_id, first_name, last_name, hire_date, salary)
SELECT nh_id, fname, lname, start_date, base_pay
FROM new_hires
WHERE start_date >= '2025-03-01';

Example E - Upsert ("insert or update if exists")

INSERT INTO employees (emp_id, first_name, last_name, salary)
VALUES (3, 'Chloe', 'Fernandez', 73000.00)
ON DUPLICATE KEY UPDATE
  salary = VALUES(salary);   -- raises Chloe's salary if emp_id=3 already exists

---

Practical Use Cases

Scenario	Business Problem	How INSERT INTO Helps
New product launch (ecommerce)	Add SKU records to catalog	INSERT INTO products ... populates searchable inventory instantly
Employee onboarding (HR)	Record new hires with default benefits	Singlerow insert or bulk from HRIS CSV
IoT sensor logging	Millions of readings per day	Batch inserts inside transactions for throughput
Audit trail	Track every user login	Trigger or application code inserts event rows
Data migration	Move legacy data into modern schema	INSERT INTO target SELECT ... FROM source transforms and loads

---

Common Mistakes & Troubleshooting

Mistake	Error / Symptom	Fix
Mismatched column & value counts	"Column count doesn't match value count at row 1"	List columns explicitly or match table definition
Forgetting quotes around strings/dates	"Unknown column 'John' in 'field list'"	Use 'John', not John
Violating unique constraint	Error 1062	Use INSERT IGNORE or handle conflict with ON DUPLICATE KEY UPDATE
Inserting NULL into NOT NULL column	Error 1048	Provide a value or set a default

Debug tips

• Run DESCRIBE table_name; to confirm column order & constraints.
• Wrap multiple inserts in a transaction and ROLLBACK to undo test data.
• Use SHOW WARNINGS; after INSERT IGNORE to see skipped rows.

---

Best Practices

• Use column lists - safer against tablechange breakage.
• Batch inserts - combine rows: INSERT ... VALUES (...), (...), ... or load files with LOAD DATA INFILE for speed.
• Prepared statements / parameter binding - prevents SQL injection and speeds repeated inserts.
• Disable autocommit during bulk loads then COMMIT once.
• Index wisely - too many indexes slow inserts; add only those needed for queries.
• Use proper data types & lengths to avoid unnecessary storage and conversions.
• When not to use INSERT INTO:
  • For analytical data loads > GBs, consider MySQL's LOAD DATA or ETL tools.
  • For constant highvelocity timeseries, specialized storage engines (e.g., InnoDB compression or columnstore) might be better.

---

HandsOn Practice

Setup (run once)

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
  cust_id    INT PRIMARY KEY,
  first_name VARCHAR(30),
  last_name  VARCHAR(30),
  city       VARCHAR(40),
  credit     DECIMAL(10,2) DEFAULT 0
);

Exercise 1 - Easy

Insert one customer with ID = 1, name "Evan Lee", city "Denver", credit 200.00.

INSERT INTO customers
VALUES (1, 'Evan', 'Lee', 'Denver', 200.00);

---

Exercise 2 - Medium

Insert three customers (IDs 24) in a single statement. Omit the credit column so it defaults to 0.

INSERT INTO customers (cust_id, first_name, last_name, city)
VALUES
  (2, 'Fatima', 'Rahman', 'Seattle'),
  (3, 'Gus',    'Novak',  'Austin'),
  (4, 'Hana',   'Ito',    'Boston');

---

Exercise 3 - Challenging

You receive a staging table vip_upgrades(vip_id, bonus_credit) containing:

vip_id	bonus_credit
2	100.00
4	150.00
5	200.00

Insert the new VIPs into customers; if the customer already exists, update their credit by adding the bonus instead of replacing it.

INSERT INTO customers (cust_id, credit)
SELECT vip_id, bonus_credit FROM vip_upgrades
ON DUPLICATE KEY UPDATE
  credit = credit + VALUES(credit);

---

Connection to Other Concepts

• Prerequisites - understanding of tables (CREATE TABLE), data types, and constraints (primary/unique keys).
• Related DML commands - after inserting, you'll often SELECT to verify, UPDATE to modify, or DELETE erroneous rows.
• Transactions - group several INSERTs for atomicity.
• Next steps - learn bulk loading (LOAD DATA), prepared statements, and index tuning for largescale inserts.

---

Concept Map

flowchart LR
    A[Schema Context] --> B[INSERT INTO]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

INSERT INTO table_name (col1, col2) VALUES (val1, val2);
INSERT INTO table_name (id, name) VALUES (1, "Alice"), (2, "Bob");

What's Next

• Previous: ORDER BY - Review the previous lesson to reinforce context.
• Next: UPDATE - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.