A new row can fail to insert when its values do not match the table’s columns. To insert data into a SQL table, name the columns and provide a value for each one in the same order. An INSERT statement in SQL can add one row or several, while omitted columns can receive defaults.
The following examples use PostgreSQL and build on one another. Run them in order against a fresh practice database. Other databases support the general idea, but identity columns, default syntax, and ways to return generated IDs differ by dialect.
Insert Statement SQL Examples: Start With a Table
Create a small table for tasks:
CREATE TABLE tasks (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title varchar(100) NOT NULL,
priority integer NOT NULL DEFAULT 1,
note text
);
Each row has a generated id, a required title, a required priority that defaults to 1, and an optional note. GENERATED ALWAYS AS IDENTITY is PostgreSQL syntax for an identity column; PostgreSQL generates the ID when it is omitted. A primary key requires unique, non-null values. For the expected IDs below, use this newly created table and run the inserts once in the order shown.
The SQL INSERT syntax places the target table and columns before VALUES. Each parenthesized group after it supplies the corresponding values. Begin with this SQL INSERT example:
INSERT INTO tasks (title, priority, note)
VALUES ('Write tests', 2, 'Cover login');
The result is one row: id 1, title Write tests, priority 2, and note Cover login. Text literals use single quotes; 2 is a number. The database assigns the ID because it is not in the column list. This SQL INSERT INTO statement makes the mapping explicit: first title, then priority, then note.
Insert more than one row
A single insert query in SQL can supply several value groups separated by commas:
INSERT INTO tasks (title, priority, note)
VALUES
('Review logs', 1, 'Check errors'),
('Update docs', 3, NULL);
The first group creates ID 2 with priority 1 and the note Check errors. The second creates ID 3 with priority 3 and a missing note. Each group must have the same number of values as the column list and use its order. SQL INSERT INTO VALUES syntax can express a batch in one statement, while still mapping each row independently.
To inspect the rows after these inserts, run:
SELECT id, title, priority, note
FROM tasks
ORDER BY id;
The query returns IDs 1 through 3 in order. The SQL INSERT row count reported by the client tells you how many rows the command added; compare it with the number you intended to create. Without ORDER BY, row order is not guaranteed. An SQL INSERT command changes the table; the SELECT reads it so you can verify the outcome. Check the actual values as well as the count, because a statement can succeed even when compatible values were supplied in the wrong order. To insert rows into a table with SQL safely, use a small test batch and inspect its result before running a larger one.
In SQL, add to the table a row specifying only the columns you intend to supply; the next example lets the database fill the rest. A SQL statement that inserts a row can therefore be shorter while still producing a complete record.
Use defaults and NULL deliberately
Leave a column out when its defined default is the value you want:
INSERT INTO tasks (title)
VALUES ('Plan release');
The resulting row has ID 4, priority 1, and a NULL note. The identity supplies id; the declared default supplies priority. Since note has no explicit default, its default is NULL. Omission and explicit NULL are different choices: a default is used on omission, while explicitly supplying NULL requests a missing value.
In PostgreSQL, you can also request the default explicitly in an insert statement:
INSERT INTO tasks (title, priority, note)
VALUES ('Fix alerts', DEFAULT, NULL);
This creates ID 5 with priority 1 and a NULL note. If you write NULL for priority instead, the insert fails because that column is NOT NULL; NULL does not trigger its default. The insert statement SQL examples here use PostgreSQL’s DEFAULT in the values list, so check your database’s documentation before transferring that form to another dialect.
A SQL query that inserts data can also provide an explicit value equal to the default, but that hard-codes today’s default into the query. Omitting the column lets the database use its current definition. This difference matters if the schema default later changes.
Retrieve a generated ID
In PostgreSQL, append RETURNING when your application needs the generated ID immediately:
INSERT INTO tasks (title, priority)
VALUES ('Ship release', 2)
RETURNING id, title;
On the fresh example table, the result contains ID 6 and title Ship release; the new row also has priority 2 and a NULL note. RETURNING is a PostgreSQL feature whose equivalent differs across database systems. Do not infer an ID by adding one to the previous result: sequences can have gaps, including after failed or rolled-back operations. Ask the database for the value it generated.
This operation supplies only the data the application owns. The database supplies the identity, and the returned row links the application to the record it just created. If an application accepts user input, use its database driver’s parameterized statements rather than joining input into SQL text.
Put the insert in a repeatable script
To reproduce setup in another environment, insert a SQL statement into a migration file managed by your project. A migration containing sample data should have a clear purpose and run after table creation. If the script is run twice, the rows may be duplicated or a constraint may reject the second run; choose a project-specific strategy for repeat execution.
For a small manual test, insert a SQL query into a script and run it against a practice database. Save the SELECT that checks the result alongside the insert. Keep the SQL statement used for insertion in version control so its intent and changes can be reviewed. An insert query written in SQL is easier to review when the target columns and expected values are visible together. If you add a query that inserts data in SQL to an application, bind external values as parameters through the driver.
Check the mapping when an insert fails
The most common mistake is a mismatch between the number or order of columns and values. For example, (title, priority) VALUES ('Audit') has two columns but only one value, so the database reports a count error. Add the missing priority or omit that column to use its default. If the counts match but the database reports a type error, compare each value with the type of its corresponding column.
Finally, check constraints and quoting. A missing title violates NOT NULL; duplicate explicit primary keys violate uniqueness; text needs single quotes. Read the database’s error, locate the named column or constraint, correct the input, and rerun the insert. When you insert data into a table using SQL, follow it with a SELECT to confirm that the stored values are the ones you intended.









