Features
SQL’s INSERT INTO statement adds new rows to a table. Provide the target table, optional column list, and one or more value sets or a SELECT query. Use transactions for safety, match data types, and specify only the columns you populate. Follow best practices to avoid errors and ensure performance.
The INSERT INTO statement lets you add new rows to a relational table using a VALUES list or a SELECT query. Match data types, supply columns explicitly, and wrap multiple statements in a transaction for safety.
INSERT INTO writes one or more new rows into a specified table. The statement can accept literal values or copy data from another result set. It modifies the target table permanently once the transaction commits.
Most engines follow this pattern: INSERT INTO schema.table (col1, col2) VALUES (val1, val2);. The column list is optional if you supply every column in order, but including it prevents mistakes when schemas change.
Provide one VALUES tuple matching the column list. Example: INSERT INTO users (email, created_at) VALUES ('lee@example.com', NOW());. The database appends one row and returns a success message or generated key.
Add comma-separated VALUES tuples: INSERT INTO products (sku, price) VALUES ('A1', 9.99), ('B2', 19.99);. This reduces network round-trips and keeps inserts atomic.
Use INSERT…SELECT when you need to copy data from existing tables or complex queries. Example: INSERT INTO archive_orders SELECT * FROM orders WHERE status = 'closed';. The database streams rows directly, making large moves efficient.
If a column has a DEFAULT clause, omit it from the column list or use the keyword DEFAULT in its place. The engine fills the value automatically at insert time.
Explicitly pass NULL for nullable columns. If a column is NOT NULL and lacks a DEFAULT, you must supply a value or the insert fails with a constraint error.
List just the columns you populate: INSERT INTO tickets (title, priority) VALUES ('Login bug', 'high');. Unlisted columns must have defaults or allow NULL.
Most drivers expose RETURNING (PostgreSQL), OUTPUT (SQL Server), or LAST_INSERT_ID() (MySQL). Example: INSERT INTO posts (title) VALUES ('Hello') RETURNING id; instantly gives the new primary key.
Wrap related inserts in BEGIN/COMMIT. If any step fails, issue ROLLBACK to undo all changes. Transactions guarantee atomicity and prevent partial writes.
For millions of rows, prefer engine-specific bulk loaders like COPY (PostgreSQL) or LOAD DATA INFILE (MySQL). These bypass row-by-row overhead and parse data in streams.
Always name columns, validate data types, batch small inserts, avoid wide transactions, and monitor indexes. Use prepared statements to prevent SQL injection and leverage RETURNING to remove round-trips.
INSERT INTO is the gateway for new data. Supply clear column lists, use VALUES for literals and SELECT for copies, wrap multi-step operations in transactions, and adopt engine-specific bulk tools for large loads.
Core syntax is standardized, but options like RETURNING or INSERT IGNORE are vendor-specific. Check your engine’s documentation for extensions.
Most engines let you call the procedure inside an INSERT…SELECT if it returns a result set. Alternatively, have the procedure perform the insert internally.
Limits vary. MySQL allows millions of VALUES tuples, while SQL Server limits the statement size to 65,536 rows. For very large sets, switch to bulk loaders.
No. It only adds new rows. To update existing ones, use UPDATE or an upsert pattern such as INSERT…ON CONFLICT in PostgreSQL.
.avif){kind=logo}
