SQL online course

SQL INSERT INTO Statement

Learn how to use SQL INSERT INTO to add one or more rows, map values to columns, use defaults and NULL, handle generated keys, and verify inserted data.

INSERT INTO is the SQL statement used to add one or more new rows, also called records, to an existing table. A table stores related data in rows and columns: a row is one complete record, while a column is a named field with a data type and rules.

INSERT INTO creates new records. It does not retrieve records like SELECT, change existing records like UPDATE, or remove records like DELETE.

Every inserted row must satisfy the target table's schema and constraints. For example, values must use compatible data types, required columns must receive values, and primary key or UNIQUE values must not conflict with existing rows.

Basic INSERT INTO Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

• INSERT INTO identifies the operation: add rows to a table.
• table_name is the destination table.
• The column list is the parenthesized list of destination columns after the table name.
• VALUES introduces the expressions that supply data.
• The values are matched to the named columns by position. The first value goes to the first column, the second value goes to the second column, and so on.

For example, this statement places 'Maya Chen' in name, '14 King Street' in address, and 'Ottawa' in city:

INSERT INTO Employees (name, address, city)
VALUES ('Maya Chen', '14 King Street', 'Ottawa');

Why Explicitly Name the Columns?

It is best practice to explicitly name the destination columns. This makes the statement readable and prevents values from depending on the table's physical column order. It also makes an insert safer if columns are later added or their order changes.

The number of expressions in the VALUES list must exactly match the number of columns in the explicit column list.

Any column omitted from the column list must be able to receive a configured default, NULL, or a database-generated value. Otherwise, the insert fails.

Employee-Table Walkthrough

Suppose an Employees table has these columns: an identifier, employee name, street address, city, state or region, and postal code. The following sample shows the table before insertion.

Insert one new employee by naming every displayed column:

INSERT INTO Employees (id, name, address, city, state, zip)
VALUES (6, 'John Doe', '1234 North Road', 'Toronto', 'CA', '98765');

The resulting table contains one additional row:

Matching Values to Column Definitions

Use a literal form that is compatible with each destination column. Character data generally requires quotes, while ordinary numeric literals do not. Date and time literal syntax varies between database products, so check the rules for the SQL engine you use.

INSERT INTO Events (event_name, attendee_count, event_date, notes)
VALUES ('SQL Workshop', 25, '2026-08-17', NULL);

• 'SQL Workshop' is quoted character data.
• 25 is an ordinary numeric literal and is not quoted.
• '2026-08-17' is a commonly accepted date representation, but exact date syntax can vary.
• NULL means missing or unknown data. It is not the same as zero, an empty string, or the quoted text 'NULL'.

Do not add quotes merely because a value looks numeric. For example, whether a postal code should be written as 98765 or '98765' depends on the column's data type. Postal codes are frequently stored as character data because they can contain letters, leading zeroes, or punctuation.

Defaults, NULL, and Generated Identifiers

Omitting columns with defaults

A column can be left out when the table can supply its value automatically. The omitted column might have a configured default, permit NULL, or be generated by the database.

DEFAULT requests the configured default value for a column where the database product supports this form:

INSERT INTO Employees (name, address, city, state, zip)
VALUES ('Ava Patel', DEFAULT, 'Hope', 'CA', '98765');

You can also omit a column entirely when its default applies:

INSERT INTO Employees (name, city, state, zip)
VALUES ('Ava Patel', 'Hope', 'CA', '98765');

This statement assumes that address can be omitted and that id is generated, has a default, or otherwise does not require a manually supplied value. Confirm the table definition before relying on that behavior.

NULL values

NULL represents an unknown or absent value and must be written without quotes. Use it only when the destination column permits NULL:

INSERT INTO Employees (name, address, city, state, zip)
VALUES ('Alex Rivera', NULL, 'Toronto', 'CA', '98765');

'NULL' is a character string containing four letters; it is not a NULL value.

Generated identifiers

A primary key uniquely identifies a row. An identity, auto-increment, or sequence-backed column is a mechanism that can generate a numeric key for each new row. When the database generates the key, ordinarily omit that column from the column list rather than choosing a value manually.

INSERT INTO Employees (name, address, city, state, zip)
VALUES ('John Doe', '1234 North Road', 'Toronto', 'CA', '98765');

If the identifier is not generated and has no default, you must provide a valid unique value.

Inserting Multiple Rows

Many SQL database engines support several parenthesized value groups in one INSERT statement:

INSERT INTO Employees (name, city, state, zip)
VALUES
  ('Ava Patel', 'Hope', 'CA', '98765'),
  ('Noah Kim', 'Harmony', 'AZ', '98765');

Every row group must use the same column order, and every group must contain the same number of values. Support and exact syntax can vary by database engine, so consult the documentation for your product when portability matters.

Constraints and Data Integrity

A constraint is a rule that restricts allowed data. Inserts are checked against constraints before the database accepts the row. Common examples include:

• PRIMARY KEY: identifies each row uniquely. A duplicate key is rejected.
• FOREIGN KEY: requires a referenced value to exist in a related parent table.
• UNIQUE: prevents duplicate values in a column or column combination.
• NOT NULL: requires a value; the column cannot be omitted without a usable default or assigned NULL.
• CHECK: requires a value to satisfy a specified condition, such as a nonnegative quantity.
• Data type rules: require values to be compatible with the destination column, such as a number for an integer column.

Learn more about these rules in the guide to SQL constraints and review column definitions with CREATE TABLE.

Verify an Inserted Row

After executing an insert, query the row and check that the stored values are what you expected:

SELECT id, name, address, city, state, zip
FROM Employees
WHERE name = 'John Doe';

If the identifier is known, filtering by the primary key is usually more precise:

SELECT *
FROM Employees
WHERE id = 6;

The WHERE clause limits which rows are returned. A verification query is especially useful when defaults, generated identifiers, date conversion, or triggers may change the final stored result.

Transactions: Commit or Roll Back

A transaction is a unit of database work that can be committed or rolled back. In an environment using explicit transaction control, commit a successful insertion to make it persistent:

COMMIT;

If a test insert is incorrect and has not been committed, undo it with:

ROLLBACK;

Transaction behavior and client defaults differ. If an inserted row is not visible later, check whether the statement was rolled back, whether it was committed, and whether transaction isolation affects what your session can see.

Troubleshooting INSERT INTO Errors

INSERT INTO Checklist

1. Confirm the destination table and its column definitions.
2. Prefer an explicit column list.
3. Make the number and order of values match the listed columns.
4. Use data-type-appropriate literals, including quoted character data and unquoted NULL.
5. Omit generated key columns unless your database requires a different form.
6. Check primary key, foreign key, UNIQUE, NOT NULL, CHECK, and type constraints.
7. Verify the new row with a precise SELECT.
8. Commit or roll back the transaction when explicit transaction control is in use.