Chapter 7: Inserting Data (DML)
Up until now, we’ve been architects: designing databases, creating tables, choosing perfect data types, and adding strong constraints. Now it’s time to become data entry specialists (but the smart, efficient kind 😄).
In SQL, the main command for adding data is INSERT INTO — part of DML (Data Manipulation Language). Today we’re going to master three very important ways to insert data:
- Basic INSERT INTO – Adding one row at a time
- Inserting multiple rows in one command (super efficient!)
- INSERT … SELECT – Copying data from one table to another (very powerful!)
We’ll continue using our bookshop database. Let’s first quickly set up the tables we need (you can copy-paste this if you want to follow along).
Quick Setup – Our Tables
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
USE bookshop; -- Authors table CREATE TABLE IF NOT EXISTS authors ( author_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, country VARCHAR(50) DEFAULT 'India' ); -- Books table CREATE TABLE IF NOT EXISTS books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, isbn CHAR(13) UNIQUE NOT NULL, author_id INT UNSIGNED NOT NULL, price DECIMAL(10,2) NOT NULL, stock SMALLINT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (author_id) REFERENCES authors(author_id) ); |
Now — let’s start inserting!
1. Basic INSERT INTO – Adding One Row
Syntax (most common style):
|
0 1 2 3 4 5 6 7 |
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); |
Important rules:
- You must provide values for all NOT NULL columns (unless they have DEFAULT)
- Order of columns in () must match order of values
- Strings go in single quotes‘like this’
- Numbers do not need quotes
- Dates usually in ‘YYYY-MM-DD’ format
Real example – Adding one author:
|
0 1 2 3 4 5 6 7 |
INSERT INTO authors (name, country) VALUES ('Ruskin Bond', 'India'); |
Adding one book (notice we use the author_id we just created):
|
0 1 2 3 4 5 6 7 8 9 10 |
-- First, let's see what author_id Ruskin Bond got SELECT author_id, name FROM authors; -- Suppose it's 1 INSERT INTO books (title, isbn, author_id, price, stock) VALUES ('The Room on the Roof', '9780143333401', 1, 250.00, 45); |
Alternative style (insert without listing columns – values must match ALL columns in order):
|
0 1 2 3 4 5 6 7 |
INSERT INTO authors VALUES (NULL, 'Arundhati Roy', 'India'); -- NULL for auto-increment id |
Pro Tip: Always list the columns explicitly — it’s safer and clearer. If someone changes the table later (adds a column), your query won’t break.
2. Inserting Multiple Rows at Once (Very Efficient!)
You can insert many rows in one single command — this is much faster than 100 separate INSERTs.
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 |
INSERT INTO table_name (column1, column2, ...) VALUES (value1a, value2a, ...), (value1b, value2b, ...), (value1c, value2c, ...); |
Real example – Add 4 authors at once:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO authors (name, country) VALUES ('Chetan Bhagat', 'India'), ('Jhumpa Lahiri', 'USA'), ('Amish Tripathi', 'India'), ('Sudha Murty', 'India'); |
Add 5 books at once:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
INSERT INTO books (title, isbn, author_id, price, stock) VALUES ('2 States', '9788129115300', 3, 299.00, 120), -- Amish ('The White Tiger', '9781416562603', 2, 399.00, 35), -- Jhumpa ('Wise and Otherwise', '9780143062226', 5, 199.00, 80), -- Sudha ('The Immortals of Meluha','9789380658742', 3, 349.00, 65), -- Amish ('The Blue Umbrella', '9780143333784', 1, 180.00, 50); -- Ruskin |
Why is this better?
- Fewer round-trips to the database → much faster
- In one transaction (safer — all succeed or none do)
3. INSERT … SELECT – Copying Data from One Table to Another
This is one of the most powerful INSERT techniques! You insert rows by selecting them from another table (or even the same table).
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
INSERT INTO target_table (column1, column2, ...) SELECT column1, column2, ... FROM source_table WHERE condition; |
Real example – Create a backup table and copy all books into it:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- First create a backup table CREATE TABLE books_backup LIKE books; -- Now copy all current books into backup INSERT INTO books_backup (title, isbn, author_id, price, stock, created_at) SELECT title, isbn, author_id, price, stock, created_at FROM books; |
More useful example – Copy only expensive books (> ₹300) to a premium table:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE premium_books ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, price DECIMAL(10,2) NOT NULL ); INSERT INTO premium_books (title, price) SELECT title, price FROM books WHERE price > 300.00; |
Even cooler – Copy with transformation:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Create a report table with discounted prices CREATE TABLE books_on_sale ( title VARCHAR(255), sale_price DECIMAL(10,2) ); INSERT INTO books_on_sale (title, sale_price) SELECT title, price * 0.80 -- 20% discount FROM books WHERE stock > 50; |
Important Tips & Best Practices (2026 Style)
| Tip | Why it matters |
|---|---|
| Always list columns | Safer when table structure changes |
| Use single quotes for strings | ‘Mumbai’, not “Mumbai” (double quotes = identifiers in some DBs) |
| Escape single quotes inside strings | ‘O”Reilly’ (two single quotes = one quote) |
| Use transactions in production | START TRANSACTION; INSERT…; COMMIT; or ROLLBACK; |
| Batch large inserts (1000–5000 rows) | Avoids memory issues and is faster |
| Check constraints first | Make sure your data satisfies NOT NULL, UNIQUE, FOREIGN KEY, etc. |
| Use IGNORE if you want to skip duplicates | INSERT IGNORE INTO … (MySQL/MariaDB) |
Common Mistakes & How to Fix Them
| Mistake | Error Message / Problem | Fix |
|---|---|---|
| Forgetting quotes around strings | Syntax error | Always ‘string’ |
| Wrong number of columns/values | Column count doesn’t match value count | Match them exactly |
| Violating FOREIGN KEY | Cannot add or update a child row | Make sure referenced row exists first |
| Inserting duplicate UNIQUE value | Duplicate entry for key | Check with SELECT before insert or use IGNORE |
| Forgetting NOT NULL fields | Field cannot be null | Provide value or set DEFAULT |
Full Fun Example – Let’s Populate the Bookshop!
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Add more authors INSERT INTO authors (name, country) VALUES ('R.K. Narayan', 'India'), ('Khushwant Singh', 'India'); -- Add many books in one go INSERT INTO books (title, isbn, author_id, price, stock) VALUES ('Malgudi Days', '9780140183917', 6, 220.00, 90), -- R.K. Narayan ('Train to Pakistan', '9780140116144', 7, 280.00, 60), -- Khushwant ('The Guide', '9780143039648', 6, 250.00, 75), ('I Too Had a Love Story', '9780143418764', 3, 199.00, 150); -- Chetan |
