VMware ESXi and vSphere Cluster Management

SQL INSERT INTO Statement: Add Rows to a Table

Learn how to use SQL INSERT INTO with explicit columns, VALUES, data types, NULL, validation queries, and constraints to add rows safely.

The SQL INSERT INTO statement adds one or more new rows to an existing database table. A table is a structured collection of columns and rows, and a row is one stored record.

INSERT creates a new record. It does not change an existing record. To modify data already stored in a row, use UPDATE instead.

Basic INSERT INTO syntax

INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, value_2, value_3);

The table name identifies where the new row will be stored. The parenthesized column list names the target columns in a specific order. The VALUES clause supplies one value for each listed column.

Values are assigned by position:

  • value_1 is inserted into column_1.
  • value_2 is inserted into column_2.
  • value_3 is inserted into column_3.

Choosing the target table and columns

The table name tells the database which table receives the new row. Listing columns explicitly is recommended because it documents the intended value order and avoids depending on the table's physical column order.

Only columns that receive supplied values need to appear in the list when omitted columns are allowed to be NULL, have a default value, or are generated automatically. A column defined as NOT NULL without a default generally must receive a valid value.

For example, if id is generated by the database, it may be omitted:

INSERT INTO Employees (name, city)
VALUES ('John Doe', 'Toronto');

Whether this works depends on the table definition and database system.

Values and SQL data types

Each supplied value must be compatible with its target column.

  • Text: Text values are string literals and are generally enclosed in single quotes, such as 'Toronto'.
  • Numbers: Numeric values are normally written without quotes, such as 6. Some systems perform implicit conversion, but relying on it can hide data errors.
  • NULL: NULL is a special marker for missing, unknown, or inapplicable data. It is not the same as the quoted text 'NULL'.
  • Dates, times, and Boolean values: Literal formats and Boolean syntax can vary among database systems.
  • Generated keys: Identity, auto-increment, sequence, and generated-key behavior also varies by database system.
INSERT INTO Contacts (name, phone, nickname)
VALUES ('Ava Lee', 5551234, NULL);

In this example, name receives text, phone receives a number, and nickname receives a missing-value marker.

Worked example: inserting an employee

Suppose an Employees table has these columns: an identifier, employee name, street address, city, state or region, and postal code.

Employees sample data

Columns: id | name | address | city | state | zip

1 | Bill Smith | 123 Main Street | Hope | CA | 98765

2 | Mary Smith | 123 Dorian Street | Harmony | AZ | 98765

3 | Bob Smith | 123 Laugh Street | Hope | CA | 98765

4 | Chang Chao | 123 Dorian Street | Hong Kong | CN | 98765

5 | John Smith | 123 Winges Road | Toronto | CA | 98765

To add a sixth employee, provide values in exactly the same order as the column list:

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

The value mapping is:

  • 6 goes into id.
  • 'John Doe' goes into name.
  • '1234 North Road' goes into address.
  • 'Toronto' goes into city.
  • 'CA' goes into state.
  • '98765' goes into zip.

This statement creates a separate row. The original five employees remain unchanged.

Employees after insertion

Columns: id | name | address | city | state | zip

1 | Bill Smith | 123 Main Street | Hope | CA | 98765

2 | Mary Smith | 123 Dorian Street | Harmony | AZ | 98765

3 | Bob Smith | 123 Laugh Street | Hope | CA | 98765

4 | Chang Chao | 123 Dorian Street | Hong Kong | CN | 98765

5 | John Smith | 123 Winges Road | Toronto | CA | 98765

6 | John Doe | 1234 North Road | Toronto | CA | 98765

INSERT compared with UPDATE

INSERT INTO creates a new row. UPDATE changes values in rows that already exist.

INSERT INTO Employees (id, name, city)
VALUES (6, 'John Doe', 'Toronto');

UPDATE Employees
SET city = 'Ottawa'
WHERE id = 6;

The first statement adds employee 6. The second changes the city of the existing row whose identifier is 6. An UPDATE without an appropriate WHERE clause can modify many rows, so use it carefully.

Inserting multiple rows

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

INSERT INTO Employees (id, name, city)
VALUES
  (7, 'Sam Green', 'Toronto'),
  (8, 'Lee Brown', 'Montreal');

Each parenthesized group represents one new row. Every group must contain values in the same order and count as the column list.

Verifying an inserted row

Use SELECT to inspect the table after insertion. Filtering by the inserted identifier makes the check precise:

SELECT id, name, address, city, state, zip
FROM Employees
WHERE id = 6;

You can also list all employees:

SELECT id, name, address, city, state, zip
FROM Employees
ORDER BY id;

SQL does not guarantee the order of rows in a result unless an ORDER BY clause is used. An inserted row is not guaranteed to appear at the displayed bottom simply because it was added last.

Data-integrity considerations

A constraint is a rule enforced by the database to protect valid data. Check these rules before inserting:

  • Primary key: A primary key is a column, or combination of columns, that uniquely identifies each row. A supplied primary-key value must not duplicate an existing value.
  • NOT NULL: A required column must receive a value unless the database supplies one through a default or generated value.
  • Data type and size: Values must fit the target type, permitted length, precision, and scale.
  • Other constraints: Values must satisfy rules such as UNIQUE, CHECK, and permitted ranges.
  • Foreign keys: When a column references a row in another table, the referenced parent row usually must already exist.

For example, inserting an order for a customer may fail if the customer identifier does not exist in the related Customers table.

Common INSERT errors and fixes

  • Column count does not match value count: The column list and VALUES list contain different numbers of items. Add or remove values so every listed column has exactly one value.
  • Values appear in the wrong fields: The values do not follow the stated column order. Recheck each position and align it with the intended column.
  • Duplicate identifier or primary-key error: The supplied ID already exists. Use a unique identifier, or omit a generated key column when the database creates it automatically.
  • Text or date syntax error: A literal is incorrectly quoted or formatted. Use single quotes for text and follow the target database's date and time format.
  • Cannot insert NULL or a missing required value: A required column was omitted or assigned NULL. Supply a valid value or use an intentional default where appropriate.
  • Inserted row is not in the expected position: The query has no defined ordering. Add ORDER BY id or another suitable column.

Key points to remember

  • INSERT INTO adds new rows; UPDATE changes existing rows.
  • Use an explicit column list whenever possible.
  • Match every value to the column in the same position.
  • Use single quotes for text literals, unquoted numeric values for numbers, and unquoted NULL for missing data.
  • Check primary keys, required columns, data types, and foreign-key relationships before inserting.
  • Use SELECT with WHERE to verify one inserted row and ORDER BY for predictable display order.

For related practice, review the SQL INSERT INTO statement guide.