INSERT is the SQL data manipulation statement that adds new rows to a table, either from explicit values or from the result of a query. Also called SQL INSERT, it belongs to the DML family together with UPDATE and DELETE, and in every relational database it is the standard way to create new records while the engine enforces the table’s data types, constraints and triggers.
In Databases work, INSERT looks simple, but its behavior around default values, generated keys, constraint violations, transactions and bulk loading determines both data quality and write throughput. Each major system, including PostgreSQL, MySQL, SQL Server, Oracle and SQLite, implements the core standard syntax and adds its own extensions.
Syntax and forms of the INSERT statement
The basic form names the target table, an optional column list and a VALUES clause: INSERT INTO customers (name, email) VALUES ('Ana Novak', 'ana@example.com');. Columns omitted from the list receive their default value, or NULL if no default is defined and the column allows it. Listing columns explicitly is strongly recommended, because it keeps the statement correct when columns are later added or reordered.
The table compares the main forms supported across SQL databases.
| Form | Example | Use |
|---|---|---|
| Single-row VALUES | INSERT INTO t (a, b) VALUES (1, 'x') | Adding one record |
| Multi-row VALUES | ... VALUES (1, 'x'), (2, 'y'), (3, 'z') | Small batches in one round trip |
| INSERT … SELECT | INSERT INTO archive SELECT * FROM orders WHERE ... | Copying or transforming existing data |
| DEFAULT VALUES | INSERT INTO t DEFAULT VALUES | Row made only of defaults |
| With returned keys | ... RETURNING id or OUTPUT inserted.id | Getting generated identifiers |
INSERT ... SELECT is particularly powerful because it moves data entirely inside the database engine, without transferring rows to the client, and it can include joins, filters and aggregations in the source query.
What the database does during an INSERT
An INSERT is more than writing a row. The engine validates and converts each value to the column type, fills defaults and generated values such as identity columns or sequences, checks constraints, updates every index on the table, fires triggers and writes the change to the write-ahead log or transaction log before the transaction commits.
| Step | What is checked or done | Failure result |
|---|---|---|
| Type conversion | Values cast to column types | Conversion or overflow error |
| NOT NULL check | Required columns have values | Constraint violation |
| Primary key and unique | No duplicate key values | Duplicate key error |
| Foreign key | Referenced parent row exists | Referential integrity error |
| CHECK constraint | Custom rule holds, such as price >= 0 | Check violation |
| Index maintenance | Every index receives a new entry | Slower writes on heavily indexed tables |
| Triggers | BEFORE and AFTER logic runs | Trigger may modify or reject the row |
Because every index must be updated, each additional index slows inserts. Tables that receive heavy write traffic should carry only the indexes that queries actually use.
INSERT compared with UPDATE, UPSERT and bulk loading
INSERT always creates new rows and fails if a unique key already exists, while UPDATE changes existing rows and does nothing if none match. UPSERT combines the two: insert the row, or update it if a conflicting key exists. For very large volumes, dedicated bulk loading commands bypass much of the per-statement overhead.
| Operation | Behavior on existing key | Typical syntax |
|---|---|---|
| INSERT | Raises a duplicate key error | INSERT INTO ... VALUES |
| UPDATE | Changes matching rows only | UPDATE ... SET ... WHERE |
| UPSERT (PostgreSQL, SQLite) | Updates or skips the conflicting row | INSERT ... ON CONFLICT DO UPDATE |
| UPSERT (MySQL, MariaDB) | Updates the existing row | INSERT ... ON DUPLICATE KEY UPDATE |
| MERGE (standard SQL) | Insert, update or delete by match rules | MERGE INTO ... USING ... |
| Bulk load | Usually errors or uses a staging table | COPY, LOAD DATA, BULK INSERT |
MySQL’s INSERT IGNORE silently skips rows that violate constraints, and it can hide real data problems, so it should be used only when skipping is truly intended.
Performance of inserts at scale
The cost of an insert is dominated by round trips, commits and index maintenance, not by the row itself. Inserting 100,000 rows as 100,000 separate statements, each in its own transaction, forces the database to flush its log to disk 100,000 times, which can take minutes. The same data inserted in batches inside a few transactions often finishes in seconds.
| Volume | Recommended approach | Reason |
|---|---|---|
| One to a few rows | Single parameterized INSERT | Simple and safe |
| Hundreds to thousands | Multi-row VALUES or driver batching | Fewer round trips |
| Tens of thousands and more | Batches of about 500 to 5,000 rows per transaction | Balances log flushes and lock duration |
| Millions of rows | Native bulk load command | Minimal per-row overhead |
| Initial load of an empty table | Load first, create indexes afterward | Indexes built once instead of per row |
Very large single transactions have their own cost: they hold locks longer, grow the transaction log and take a long time to roll back if they fail. Moderate batch sizes are usually the best compromise.
Common mistakes with INSERT
Most INSERT problems come from implicit assumptions about columns, keys and input safety rather than from the syntax itself.
- Omitting the column list makes the statement break, or silently fill the wrong columns, when the schema changes.
- Building statements by string concatenation opens the door to SQL injection; use parameters.
- Reading the new key with a separate MAX(id) query is unsafe under concurrency; use
RETURNING,OUTPUTor the driver’s generated-key API. - Retrying a failed insert blindly can create duplicates when the first attempt actually committed; use a unique business key or an upsert.
- Inserting one row per transaction in loops wastes throughput; batch the work.
Understanding it simply
INSERT is the command that adds a new line to a table in a database, like writing a new entry into a ledger. The program says which table to use, which columns it is filling in and what the values are.
Before accepting the entry, the database checks that it follows the rules: required fields are filled, numbers are numbers, and nothing duplicates an entry that must be unique. If anything is wrong, the new line is refused rather than saved half-finished.
Applied in practice
A logistics company imports about two million tracking events per night from partner carriers into PostgreSQL. The first version of the import script issued one INSERT per event with autocommit enabled, and the job regularly ran for more than three hours, overlapping with morning traffic.
The team changed the script to load each file into a staging table with COPY, then move the rows with a single INSERT ... SELECT ... ON CONFLICT DO NOTHING that removes duplicates by event identifier. The nightly import now completes in about ten minutes, duplicate events from resent files are ignored safely, and the main table’s indexes are maintained in one pass per batch.