Introduction
The INSERT statement is one of the core Data Manipulation Language (DML) commands in SQL. It adds new rows of data into a table. Whether you are seeding a new database, logging an application event, or importing a batch of records, INSERT is the tool that puts data into your tables in the first place.
Cricket analogy: Just as a scorer logs a new delivery into the ball-by-ball database the instant it happens, INSERT is the command that adds new rows into a table, whether seeding a fresh tournament schedule or logging a live wicket event.
Syntax
-- Single row, explicit columns
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (101, 'Ava', 'Chen', 'Engineering', 95000);
-- Multiple rows in one statement
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES
(102, 'Liam', 'Ortiz', 'Sales', 62000),
(103, 'Priya', 'Nair', 'Marketing', 58000);
-- Insert from another table's query result
INSERT INTO employees_archive (employee_id, first_name, last_name, department, salary)
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales';Explanation
You name the target table and, optionally, the list of columns you intend to supply values for. If you omit the column list, SQL expects a value for every column in the table's exact definition order, which is fragile and not recommended. The VALUES clause supplies one or more comma-separated tuples for multi-row inserts. The INSERT...SELECT form skips VALUES entirely and instead pulls rows from a SELECT query, letting you copy or transform data between tables in a single statement. Columns not listed but defined with a DEFAULT or that allow NULL will use that default or NULL automatically; columns marked NOT NULL without a default must always be supplied.
Cricket analogy: Naming target columns like (batter, runs, balls) before VALUES is like a scorer specifying exactly which fields to fill for a new innings, while an INSERT...SELECT could copy a whole tournament's stats from last year's table into this year's, skipping manual entry.
Example
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2) DEFAULT 50000
);
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (104, 'Noah', 'Kim', 'Support');
SELECT * FROM employees WHERE employee_id = 104;Output
The row for employee 104 is created with first_name 'Noah', last_name 'Kim', department 'Support', and salary populated automatically with the column's DEFAULT value of 50000.00, since no salary was supplied in the INSERT list. Most database engines also report how many rows were affected, e.g. '1 row inserted'.
Cricket analogy: A new scorer entry for match 104 is created with team 'New Zealand', venue 'Eden Park', and toss_result populated automatically with the column's DEFAULT value of 'bat_first', since no toss result was supplied in the INSERT list, and the system confirms '1 row inserted'.
Key Takeaways
- Always list target columns explicitly to avoid errors if the table structure changes later.
- Multiple rows can be inserted in a single statement, separated by commas, which is far more efficient than one INSERT per row.
- INSERT INTO ... SELECT copies query results directly into another table without an intermediate VALUES list.
- Columns omitted from the insert use their DEFAULT value or NULL if no default is defined and the column is nullable.
- NOT NULL columns without a default must always receive an explicit value or the statement will fail.
Practice what you learned
1. Which clause lets you copy rows from one table directly into another without listing literal values?
2. What happens if you omit the column list in an INSERT statement?
3. A NOT NULL column with no DEFAULT is left out of an INSERT's column list. What occurs?
4. Which statement correctly inserts two rows in a single SQL command?
Was this page helpful?
You May Also Like
The UPDATE Statement
Modify existing rows in a table using UPDATE with SET and WHERE, and understand the risks of a missing WHERE clause.
DELETE and TRUNCATE
Compare the DELETE and TRUNCATE commands for removing rows, including logging, rollback, and identity reset behavior.
Constraints and Data Integrity
Explore how PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints enforce valid data.