INSERT
SQL · Modifying Data
Theory
INSERT adds new rows to a table. The usual form names the columns and then gives one set of values per row: INSERT INTO employees (name, salary) VALUES ('Asha Rao', 65000). Columns you leave out get their DEFAULT, an identity value, or NULL. Several rows can be added in one statement by separating value lists with commas, and INSERT INTO target (cols) SELECT ... copies rows from a query.
Every constraint is checked: a duplicate primary key, a NULL in a NOT NULL column or a failed CHECK makes the whole statement fail, and in a multi-row insert none of the rows are kept. Values are matched to columns by position, so always list the columns; without the list you must supply every column in table order, which breaks when the table changes. Engines also offer upsert forms such as MERGE, INSERT ... ON CONFLICT (PostgreSQL, SQLite) or ON DUPLICATE KEY UPDATE (MySQL).
Why we use it
Tables start empty and keep growing as new customers sign up and new orders arrive. INSERT is how every new row gets into the database, whether typed by hand, sent by an app or copied from another table.
When to use it
Use INSERT ... VALUES for a few rows and INSERT ... SELECT to copy or transform data in bulk. For very large file loads, use the engine's bulk loader (COPY, BULK INSERT, LOAD DATA). Use MERGE or upsert when a row may already exist.
How it works
- INSERT INTO employees (cols) VALUES (...)
- Match each value to its column by position
- Fill omitted columns with DEFAULT, identity or NULL
- Do all constraints pass? Yes: Add the row to the table. No: Reject the whole statement.
- Table has new rows
Syntax
INSERT INTO table (col1, col2)
VALUES (val1, val2);Example
INSERT INTO employees (name, salary)
VALUES ('Asha Rao', 65000);Key points
- Column list is optional but recommended for clarity
- Can insert multiple rows in one statement
- Omitted columns get DEFAULT, identity value or NULL
- If any row breaks a constraint, the whole statement fails
- INSERT INTO ... SELECT copies rows from a query