SQL INSERT Statement (How to Add Rows to a Database Table)

Lessons 3 and 4 covered CREATE TABLE — how to define the structure of a database table. Lessons 5 through 7 covered indexes — how to speed up queries against that structure. This lesson covers INSERT — how to put data into the table you created. Once rows are in the table, lesson 9 will cover SELECT — how to retrieve them. INSERT is the second of the four core SQL data manipulation statements: SELECT, INSERT, UPDATE, and DELETE.

The INSERT statement follows a fixed structure. Understanding each part before writing your first INSERT is the same approach used in lesson 4's CREATE TABLE walkthrough — know what each keyword means before combining them.

INSERT INTO — Naming the Target Table

The statement opens with the keywords INSERT INTO followed immediately by the name of the table that will receive the new row. INSERT is the command; INTO is required by the SQL standard; the table name identifies the target:

INSERT INTO BasicTable

This tells the engine: "I want to add a row to BasicTable." The engine verifies that BasicTable exists and that you have INSERT permission before proceeding.

INSERT INTO BasicTable
(Lastname, Firstname)

VALUES ('Gauss', 'Carl')

INSERT INTO BasicTable
(Lastname, Firstname)
VALUES ('Gauss', 'Carl');

INSERT with Column List — The Recommended Form

Using the column list form, the INSERT for BasicTable explicitly names the columns receiving values. The PhoneNumber column is omitted — it is NULL-able, so omitting it leaves the column value as NULL:

INSERT INTO BasicTable
(Lastname, Firstname)
VALUES ('Gauss', 'Carl');

INSERT INTO BasicTable
VALUES ('Carl', 'Gauss', '555-0192');

-- This will fail: BasicTable has 3 columns, only 2 values provided
INSERT INTO BasicTable
VALUES ('Carl', 'Gauss');

-- Generic SQL error (wording varies by engine):
-- ERROR: INSERT has more target columns than expressions
-- or: column name or number of supplied values does not match table definition

Single Quotes for String Literals

The ANSI SQL standard is explicit: single quotes delimit string literals — the actual text values being inserted into the database. Every string value in an INSERT statement must be wrapped in single quotes:

INSERT INTO BasicTable (Lastname, Firstname)
VALUES ('Riemann', 'Bernhard');

This is correct, portable SQL that works in PostgreSQL, MySQL, SQL Server, Oracle, and SQLite without modification.

Double Quotes for Identifiers

Double quotes have a different role in standard SQL: they delimit identifiers — table names, column names, and schema names — when those names contain spaces, special characters, or reserved words that would otherwise cause parsing errors:

-- Double quotes around an identifier that would otherwise be a reserved word
SELECT "order", "date" FROM BasicTable;

Using double quotes around string values like "Gauss" is a common mistake. Some engines — MySQL with its default settings — accept double-quoted strings, but this is non-standard behavior. PostgreSQL treats double-quoted strings as identifier references and will produce an error or return unexpected results. Write single-quoted strings from the beginning and the code will be portable across all major database engines.

Engine Behavior Variations

MySQL accepts double quotes for string literals by default unless the ANSI_QUOTES SQL mode is enabled. SQL Server accepts both single and double quotes for strings in some contexts. PostgreSQL strictly follows the standard: double quotes are identifiers only. To write portable SQL that works without modification across engines, always use single quotes for string literals.

Repeated Single-Row INSERTs

The simplest way to insert multiple rows is to execute a separate INSERT statement for each row. This populates BasicTable with the three mathematician rows used throughout this module:

INSERT INTO BasicTable (Lastname, Firstname, PhoneNumber)
VALUES ('Gauss', 'Carl', '555-0192');

INSERT INTO BasicTable (Lastname, Firstname, PhoneNumber)
VALUES ('Riemann', 'Bernhard', '555-0317');

INSERT INTO BasicTable (Lastname, Firstname, PhoneNumber)
VALUES ('Hilbert', 'David', '555-0437');

Each statement inserts one row and triggers one update to each index defined on BasicTable — as covered in lesson 7's write overhead discussion.

Multi-Row INSERT — One Statement, Many Rows

The SQL standard supports inserting multiple rows in a single INSERT statement by providing multiple value lists separated by commas after the VALUES keyword. This is more efficient than repeated single-row INSERTs because it reduces the number of round trips to the database and, in many engines, performs a single index update operation for the batch rather than one per row:

INSERT INTO BasicTable (Lastname, Firstname, PhoneNumber)
VALUES
    ('Gauss',   'Carl',     '555-0192'),
    ('Riemann',  'Bernhard', '555-0317'),
    ('Hilbert',  'David',    '555-0437');

This single statement inserts all three rows atomically — either all three are inserted successfully or none are, if a constraint violation occurs on any row. Multi-row INSERT is supported in PostgreSQL, MySQL/MariaDB, SQL Server (2008+), and SQLite. Oracle requires a different syntax using multiple SELECT clauses or a different INSERT ALL form.

Omitting the Primary Key Column

In lesson 4, the Contacts table was defined with an AUTO_INCREMENT primary key on ContactID:

CREATE TABLE Contacts (
    ContactID   INT      NOT NULL AUTO_INCREMENT,
    Firstname   CHAR(50)     NULL,
    Lastname    CHAR(50) NOT NULL,
    PhoneNumber CHAR(10)     NULL,
    PRIMARY KEY (ContactID)
);

When inserting into this table, omit ContactID from the column list. The engine assigns the next available integer value automatically — you never need to track or supply the primary key value for auto-generated keys:

INSERT INTO Contacts (Lastname, Firstname, PhoneNumber)
VALUES ('Gauss', 'Carl', '555-0192');

INSERT INTO Contacts (Lastname, Firstname, PhoneNumber)
VALUES ('Riemann', 'Bernhard', '555-0317');

INSERT INTO Contacts (Lastname, Firstname, PhoneNumber)
VALUES ('Hilbert', 'David', '555-0437');

After these three INSERTs, ContactID values 1, 2, and 3 are assigned automatically.

AUTO_INCREMENT, SERIAL, and IDENTITY

The auto-generated key feature has different syntax across engines. MySQL and MariaDB use AUTO_INCREMENT. PostgreSQL uses SERIAL (shorthand for an integer column with a sequence) or the SQL standard GENERATED ALWAYS AS IDENTITY syntax introduced in PostgreSQL 10. SQL Server uses IDENTITY(1,1). The INSERT syntax for omitting the column is the same across all engines — only the CREATE TABLE definition differs.

Omitting a NULL-able Column

When a column is defined as NULL-able in CREATE TABLE and is omitted from the INSERT column list, the engine stores NULL in that column for the inserted row. This is intentional and correct behavior — it records the absence of a value for that attribute in that record. In BasicTable, omitting PhoneNumber from the column list leaves the phone number as NULL:

-- PhoneNumber receives NULL because it is omitted
INSERT INTO BasicTable (Lastname, Firstname)
VALUES ('Euler', 'Leonhard');

Violating a NOT NULL Constraint

When a column is defined as NOT NULL and is omitted from the INSERT column list with no DEFAULT value defined, the engine rejects the INSERT with a constraint violation error. In Contacts, Lastname is NOT NULL — omitting it causes an error:

-- This fails: Lastname is NOT NULL with no default
INSERT INTO Contacts (Firstname, PhoneNumber)
VALUES ('Leonhard', '555-0512');

The error confirms that the constraint is enforced at the database level, independent of any application logic.

Inserting NULL Explicitly

NULL can also be inserted explicitly using the NULL keyword in the VALUES list. This is equivalent to omitting a NULL-able column and is sometimes useful for clarity or when inserting all columns in order:

INSERT INTO BasicTable (Lastname, Firstname, PhoneNumber)
VALUES ('Euler', 'Leonhard', NULL);

PRIMARY KEY Violation

Attempting to INSERT a row with a primary key value that already exists in the table produces a primary key violation error. The engine rejects the INSERT and the row is not added. For auto-generated keys this never occurs because the engine assigns unique values; for manually supplied keys, the application must ensure uniqueness or handle the error:

-- If ContactID 1 already exists, this INSERT fails
INSERT INTO Contacts (ContactID, Lastname, Firstname)
VALUES (1, 'Euler', 'Leonhard');
-- ERROR: duplicate key value violates unique constraint "pk_contacts"

UNIQUE Constraint Violation

A UNIQUE index or constraint on a column — such as a unique index on email address — rejects any INSERT that would create a duplicate value in that column. The error is the same class as a primary key violation:

-- If idx_unique_email exists and 'gauss@example.com' already has a row:
INSERT INTO users (email, username)
VALUES ('gauss@example.com', 'cg1777');
-- ERROR: duplicate key value violates unique constraint "idx_unique_email"

FOREIGN KEY Violation

When a table has a foreign key constraint referencing another table, any INSERT that provides a foreign key value not present in the referenced table is rejected. This enforces referential integrity — you cannot insert an order for a customer that does not exist:

-- If ContactID 99 does not exist in Contacts:
INSERT INTO Orders (OrderID, ContactID, OrderDate)
VALUES (1001, 99, '2026-04-16');
-- ERROR: insert or update on table "orders" violates
--        foreign key constraint "fk_orders_contactid"

Inserting from a Query Result

The INSERT INTO ... SELECT pattern inserts rows from a query result rather than literal values. Instead of a VALUES clause, the INSERT uses a SELECT statement — any rows returned by the SELECT are inserted into the target table. This is one of the most powerful patterns in SQL for copying, transforming, or migrating data:

-- Copy all contacts with a phone number into a PhoneDirectory table
INSERT INTO PhoneDirectory (Lastname, Firstname, PhoneNumber)
SELECT Lastname, Firstname, PhoneNumber
FROM Contacts
WHERE PhoneNumber IS NOT NULL;

The SELECT can include WHERE conditions, JOINs, expressions, and any other valid SQL — the INSERT receives whatever rows the SELECT produces. The column count and compatible data types between the SELECT output columns and the INSERT target columns must match.

Every INSERT into a table triggers an update to all indexes defined on that table. Inserting a row into BasicTable with LastnameIndex and idx_basictable_lastname_firstname defined requires two B-tree insert operations in addition to the base table insert. Multi-row INSERT is more efficient than repeated single-row INSERTs in part because some engines batch the index updates for the whole set rather than performing them one row at a time.

For tables with many indexes and high INSERT rates, this overhead is measurable. Lesson 7 covered the write overhead trade-off in detail. The practical guidance: index the columns your queries need, verify with EXPLAIN, and do not create indexes preemptively on columns with no query coverage.

After inserting rows, the standard way to verify the result is a SELECT statement against the same table. The following query retrieves all rows and all columns from BasicTable:

SELECT * FROM BasicTable;

The asterisk (*) is shorthand for "all columns." This is a useful verification tool during development and testing. In production queries, specifying column names explicitly is preferred over SELECT * — it makes the query's intent clear, avoids returning unnecessary data, and protects against unexpected results if the table's column list changes.

Lesson 9 covers the SELECT statement in full — how to retrieve specific columns, filter rows with WHERE, sort results with ORDER BY, and combine data from multiple tables with JOIN.