INSERT Statements
Overview
The INSERT statement is the foundation of Data Manipulation Language (DML). It takes raw data from your backend (like a user submitting a signup form) and physically writes a new row into the database table. You can insert a single row, multiple rows simultaneously (Bulk Insert), or even dynamically copy millions of rows from one table directly into another without ever passing the data back to the server.
Syntax
-- 1. Standard Single Row Insert
-- Always explicitly declare the columns to protect against schema changes!
INSERT INTO users (username, email, age)
VALUES ('johndoe', 'john@mail.com', 25);
-- 2. Bulk Insert (Massively faster than running 3 separate queries)
INSERT INTO products (title, price)
VALUES
('Laptop', 999.99),
('Mouse', 25.00),
('Keyboard', 150.00);
-- 3. INSERT INTO ... SELECT (Copying data between tables)
-- Instantly archives all banned users without touching the backend!
INSERT INTO banned_users_archive (user_id, email, banned_date)
SELECT id, email, CURRENT_DATE
FROM users
WHERE status = 'banned';Common Pitfalls
- Omitting the column list (
INSERT INTO users VALUES ('john', 'john@mail')). If you don't declare the columns, SQL assumes you are inserting data in the exact physical order the table was created. If a DBA later runsALTER TABLEand adds a new column in the middle, yourINSERTwill silently push the email into the new column, corrupting the database. - Inserting timestamps manually from the backend. Timezones and server latency are a nightmare. Let the database generate the timestamp perfectly using
DEFAULT CURRENT_TIMESTAMP.
Interview Questions
INSERT statement with 1,000 rows (VALUES (..), (..), (..)) significantly faster than executing 1,000 separate INSERT statements with 1 row each?Network Round Trips and Transaction Overhead. A single bulk insert requires only 1 network trip and 1 physical disk transaction commit. 1,000 separate queries require 1,000 network hops and 1,000 physical disk lock/commit cycles, completely bottlenecking the database.
Real-World Example
PostgreSQL: Returning the newly generated ID immediately after insertion so the backend can use it.
INSERT INTO users (username, email)
VALUES ('alice123', 'alice@mail.com')
-- Automatically hands the newly minted auto-incrementing ID back to Node.js!
RETURNING user_id;Check Your Knowledge
Test your understanding of INSERT Statements with these quick questions.