Modification
The INSERT Statement
The INSERT statement adds new rows to a table.
Overview
The INSERT statement adds new rows to a table. It is one of the core data modification statements in SQL, alongside UPDATE and DELETE, and has been part of the standard since SQL-86. INSERT works against base tables and, in many implementations, against updatable views. The statement can add a single row, multiple rows, or the entire result of a query. Because it changes stored data, INSERT is subject to transaction control: in transactional engines the change is not durable until the enclosing transaction commits, and it can be rolled back. Column values must be compatible with the target column data types, and constraints such as NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK are enforced at insert time. If a constraint is violated, the statement fails and no row is added (unless error handling or an upsert extension is used). INSERT is frequently combined with SELECT to copy or transform data between tables. Vendor extensions add features such as upsert, RETURNING, and bulk loading, but the core single-table form is portable across mainstream systems.
Syntax and usage
The standard single-row form is INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...). The column list is optional; when omitted, values must be supplied for every column in table order. A multi-row form is INSERT INTO t (a, b) VALUES (1, 2), (3, 4). The query form is INSERT INTO t (a, b) SELECT x, y FROM source. Columns not listed receive their default value or NULL. The VALUES clause may contain expressions, not only literals, and may reference functions. Some systems allow DEFAULT as an explicit value to request the column default. String and date literals are quoted; numeric literals are not. The target column list should always be written explicitly so that adding a column later does not silently shift values. Vendor differences: PostgreSQL, Oracle, and SQL Server support a RETURNING or OUTPUT clause to return generated values; MySQL uses LAST_INSERT_ID(); SQLite supports RETURNING in recent versions. Upsert syntax (ON CONFLICT, ON DUPLICATE KEY, MERGE) is non-standard and varies by product.
How it works
The database parses the INSERT, resolves the target table and columns, and evaluates the value expressions or the source query. Each produced row is then checked against the table definition: data type compatibility, NOT NULL, uniqueness, referential, and check constraints. If all checks pass, the row is written to the table's storage. For a query form, rows are generated by the SELECT and inserted as they are produced, so the number of inserted rows equals the number of qualifying source rows. Identity or auto-increment columns are populated by the engine when not supplied. Indexes and materialized structures are updated to reflect the new rows. In a transactional system, the changes are recorded in the transaction log and become visible to other sessions only on commit, depending on isolation level. Triggers defined on the table may fire before or after the insert, and their effects are part of the same statement. If any row fails a constraint, the whole statement is rolled back by default in most systems, though some allow per-row error handling.
Examples
Insert one row with an explicit column list: INSERT INTO employees (id, name, salary) VALUES (101, 'Ada', 72000). Insert multiple rows at once: INSERT INTO employees (id, name, salary) VALUES (102, 'Grace', 81000), (103, 'Linus', 67000). Insert from a query, copying active customers into an archive table: INSERT INTO customers_archive (id, name) SELECT id, name FROM customers WHERE active = 0. Use DEFAULT for a column: INSERT INTO employees (id, name, hired_on) VALUES (104, 'Ken', DEFAULT). Return a generated key (PostgreSQL): INSERT INTO employees (name) VALUES ('Barbara') RETURNING id. Insert with a subquery value: INSERT INTO totals (dept, amount) VALUES ('sales', (SELECT SUM(amount) FROM orders WHERE dept = 'sales')). Each example assumes the target columns exist and the supplied values satisfy the table's constraints.
Common pitfalls
Omitting the column list ties values to physical column order; a later schema change can then insert values into the wrong columns or fail. Supplying NULL to a NOT NULL column, or a duplicate key to a UNIQUE or PRIMARY KEY column, raises an error and aborts the statement. Type mismatches, such as inserting a string into a numeric column, may be rejected or silently coerced depending on the product and settings. Forgetting that the query form inserts every qualifying row can cause unintended bulk changes. In transactional systems, failing to commit leaves the rows invisible to other sessions and may hold locks. Inserting into a table with triggers can produce side effects that are easy to overlook. Very large single-statement inserts can consume substantial memory or log space; batching is often preferable. Finally, upsert and RETURNING syntax is not portable, so code relying on it may need adjustment when moving between database products.
Best practices
Always name the target columns explicitly so the statement is robust to schema changes. Use parameterized statements or bound variables rather than concatenating literals, which avoids quoting errors and injection risks. Validate data before insertion where possible, and rely on constraints as a backstop. For bulk loads, prefer multi-row VALUES or the query form over many single-row statements, and consider batching within a transaction. Commit or roll back promptly to release locks. When generated keys are needed, use the product's RETURNING, OUTPUT, or equivalent mechanism rather than a separate lookup. Keep triggers on high-volume insert paths minimal. Test inserts against a copy of the schema to confirm constraint behavior. Document any non-standard upsert usage so it can be reviewed during migration. Where the standard offers a portable form, prefer it over vendor-specific extensions unless the extension provides a clear operational benefit.
Frequently asked questions
Can INSERT add rows to more than one table at once? In standard SQL, no; a single INSERT targets one table. Some products support multi-table insert extensions, but these are vendor-specific. Can I insert without listing columns? Yes, but then you must supply a value for every column in the table's defined order, which is fragile and generally discouraged. What happens if a constraint is violated? The statement fails and, by default, no rows from that statement are kept; the exact behavior and any per-row error handling depend on the database product. Does INSERT see uncommitted rows from other transactions? Visibility follows the session's isolation level, not the INSERT statement itself; in most systems uncommitted rows from other transactions are not visible. Can INSERT be rolled back? Yes, in transactional engines, as long as the transaction has not been committed.