By the end of this lesson, you’ll be able to:
Understand how the
INSERTstatement works.Add one or multiple rows into a table.
Use
DEFAULTvalues and omit optional columns.Copy data from one table to another.
Handle errors safely with good practices.
What Does INSERT Do?
The INSERT statement is part of DML (Data Manipulation Language).
It’s used to add new rows (records) into an existing table.
Think of it as feeding data into your schema — you’ve already designed the table with CREATE TABLE, and now it’s time to fill it with content.
Basic Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
table_name: The name of the table you want to insert into.column1, column2, ...: The specific columns you want to fill.VALUES: The actual values that go into each column.
Example
Let’s use this sample table:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
email VARCHAR(100) UNIQUE,
country VARCHAR(50) DEFAULT 'Unknown',
signup_date DATE DEFAULT CURRENT_DATE
);
Now insert a new record:
INSERT INTO customers (customer_id, first_name, last_name, email, country)
VALUES (1, 'Maya', 'Chen', 'maya.chen@email.com', 'France');
✅ Result: One row is added to the table.
3️⃣ Inserting Multiple Rows at Once
Instead of repeating the same command, you can insert multiple records in one go:
INSERT INTO customers (customer_id, first_name, last_name, email, country)
VALUES
(2, 'Leo', 'Martin', 'leo.martin@email.com', 'France'),
(3, 'Sam', 'Singh', 'sam.singh@email.com', 'USA'),
(4, 'Ava', 'Smith', 'ava.smith@email.com', 'UK');
✅ Advantages:
Faster (one statement = less network overhead).
Useful for bulk loading.
4️⃣ Using Defaults and Optional Columns
If a column has a DEFAULT value, you can skip it.
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (5, 'Noah', 'Li', 'noah.li@email.com');➡️ The database automatically fills:
country→'Unknown'signup_date→ today’s date (CURRENT_DATE)
Or use explicit defaults:
5️⃣ Inserting All Columns (Not Recommended)
You can omit the column list if you provide every column value in the correct order:
INSERT INTO customers
VALUES (7, 'Liam', 'Nguyen', 'liam.nguyen@email.com', 'Canada', '2025-11-02');
⚠️ Warning:
This approach breaks easily if table structure changes.
👉 Always list column names — it’s more explicit and maintainable.
6️⃣ Copying Data from Another Table
You can insert rows based on a query (no VALUES keyword).
Example
Copy all French customers into a new table:
CREATE TABLE french_customers AS
SELECT *
FROM customers
WHERE country = 'France';
Or use INSERT INTO ... SELECT to add to an existing table:
INSERT INTO archive_customers (customer_id, first_name, last_name, country)
SELECT customer_id, first_name, last_name, country
FROM customers
WHERE signup_date < DATE '2024-01-01';
✅ Use Case: Data migrations, backups, or ETL pipelines.
7️⃣ Handling Constraints and Errors
🔸 NOT NULL
If a column is defined as NOT NULL, you must provide a value.
-- This will fail if 'first_name' is NOT NULL
INSERT INTO customers (customer_id, last_name) VALUES (10, 'Brown');
🔸 UNIQUE
Violating a UNIQUE constraint (like email) raises an error.
-- Duplicate email -> error
INSERT INTO customers (customer_id, first_name, email)
VALUES (11, 'Sarah', 'maya.chen@email.com');
🔸 FOREIGN KEY
You cannot insert a record that refers to a non-existent parent record.
INSERT INTO orders (order_id, customer_id, amount)
VALUES (101, 999, 120.00); -- will fail if customer_id 999 doesn't exist
8️⃣ Using RETURNING (PostgreSQL, SQLite)
Some databases let you return the inserted values — handy for getting IDs.
INSERT INTO customers (first_name, last_name, email)
VALUES ('Chloe', 'Garcia', 'chloe.garcia@email.com')
RETURNING customer_id;
✅ Useful in application code or ETL jobs where you need to track new IDs.
9️⃣ Inserting from External Files (Advanced Use)
Data engineers often insert data in bulk using CSV/Parquet files instead of manual queries.
PostgreSQL Example:
COPY customers (customer_id, first_name, last_name, email, country)
FROM '/path/to/customers.csv'
DELIMITER ','
CSV HEADER;
MySQL Example:
LOAD DATA INFILE '/path/to/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
✅ Use Case: Data ingestion, migration, or initializing a data warehouse.
🔟 Common Mistakes & Best Practices
| Mistake | Why It Happens | How to Fix |
|---|---|---|
| Omitting column list | Database expects all values in exact order | Always list columns |
| Violating constraints | NOT NULL, UNIQUE, or FOREIGN KEY not respected | Check schema before inserting |
| Wrong data type | E.g., inserting 'abc' into INT | Match types to schema |
| Quoting incorrectly | Using wrong quotes for strings or numbers | Use 'text' for strings, no quotes for numbers |
| Forgetting transactions | In bulk inserts or tests | Use BEGIN/ROLLBACK for safety |
1️⃣1️⃣ Transactions for Safe Inserts
Wrap inserts in transactions to avoid partial writes:
BEGIN;
INSERT INTO customers (customer_id, first_name, email)
VALUES (12, 'Aiden', 'aiden.lee@email.com');
INSERT INTO customers (customer_id, first_name, email)
VALUES (13, 'Olivia', 'olivia.liu@email.com');
COMMIT;
✅ This ensures your data integrity remains safe even if one insert fails.
✅ Summary
| Concept | Command | Example |
|---|---|---|
| Add one row | INSERT INTO ... VALUES | INSERT INTO users VALUES (...); |
| Add multiple rows | INSERT INTO ... VALUES (...), (...); | ✅ Efficient |
| Use defaults | Skip column or use DEFAULT | INSERT INTO users (...) VALUES (..., DEFAULT); |
| Copy data | INSERT INTO ... SELECT | INSERT INTO archive SELECT * FROM users; |
| Safe insert | Use BEGIN / ROLLBACK | Transactions |
| Bulk insert | COPY or LOAD DATA | ETL use case |
Key Takeaways
INSERTadds new rows — always specify columns for clarity.Default values simplify repetitive inserts.
Use transactions for safety and rollback options.
Bulk inserts (
COPY,LOAD DATA) are essential for data engineers.Respect constraints to maintain data integrity.