INSERT INTO
INSERT INTO adds new rows to a table. You can insert a single row, multiple rows at once, or insert from a SELECT query. Understanding column order, data types, and handling auto-generated values (like SERIAL/AUTO_INCREMENT IDs) is essential.
INSERT Patterns
- INSERT INTO table (cols) VALUES (vals) — single row
- INSERT INTO table (cols) VALUES (v1), (v2), (v3) — multiple rows in one statement
- INSERT INTO table SELECT ... — copy rows from another table
- Omit auto-generated columns (SERIAL / AUTO_INCREMENT)
- String values use single quotes: 'John Doe'
- NULL for missing optional values: NULL
INSERT Examples
-- Insert a single user
INSERT INTO users (name, email)
VALUES ('Sarah Connor', 'sarah@example.com');
-- Insert multiple users at once (efficient)
INSERT INTO users (name, email) VALUES
('Tom Hardy', 'tom@example.com'),
('Jane Doe', 'jane@example.com'),
('Mike Ross', 'mike@example.com');
-- Insert a product
INSERT INTO products (name, price, stock)
VALUES ('Tablet', 349.99, 75);
-- Insert an order (references existing user id=1)
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 349.99, 'pending');
-- Insert with explicit NULL
INSERT INTO payments (order_id, amount, payment_method, paid_at)
VALUES (1, 349.99, 'credit_card', NULL);
-- paid_at is NULL — payment not yet confirmed
-- Copy rows: insert from SELECT
INSERT INTO orders (user_id, total_amount, status)
SELECT id, 0.00, 'draft' FROM users WHERE created_at > '2024-01-01';Quick Quiz
Tip
Tip
Practice INSERT INTO in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Common Mistake
Warning
A common mistake with INSERT INTO is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of INSERT INTO from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- INSERT INTO adds new rows to a table.
- INSERT INTO table (cols) VALUES (vals) — single row
- INSERT INTO table (cols) VALUES (v1), (v2), (v3) — multiple rows in one statement
- INSERT INTO table SELECT ... — copy rows from another table