Insert New Records in MySQL

Learn how to add one or more rows to a MySQL table with INSERT INTO, use column lists safely, and verify records with SELECT.

What INSERT INTO Does

INSERT INTO is the MySQL statement used to create new rows in an existing table. A table stores related data in rows and columns.

A row, also called a record, is one complete item in a table.
A column, also called a field, is a named attribute that stores one kind of value for each row.
A value is the data supplied for a target column.

An INSERT operation stores the supplied values in the columns of a new row. The values must be suitable for the columns' declared data types, such as text or an integer.

Sample Table

The examples assume that an existing table named testtable has these columns:

Column name	Example data type	Example value
name	text type	Amy
surname	text type	Goodridge
year	integer type	1991

Text values are written in quotes. The year is a numeric value, so it is written without quotes. The exact data types and any constraints should be checked in the table definition before inserting data. See MySQL data types for more information.

INSERT Syntax with a Column List

The clearest general form names the destination columns explicitly:

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

table_name is the table that receives the new row.
The column list is the ordered list of destination columns after the table name.
The VALUES clause supplies the data to store.
Values correspond positionally to the columns listed. The first value goes to the first listed column, the second value to the second listed column, and so on.

The number and ordering of the listed columns and supplied values must match. For example, if the column list is (name, surname, year), the values must be supplied in that same logical order: a name, a surname, and a numeric year.

Insert One Record

This statement adds a record for Amy Goodridge:

INSERT INTO testtable (name, surname, year)
VALUES ('Amy', 'Goodridge', 1991);

The mapping is:

'Amy' is stored in name.
'Goodridge' is stored in surname.
1991 is stored in year.

When this statement succeeds in the MySQL command-line client, the response commonly includes text such as Query OK, 1 row affected. The affected-row count indicates that one table row was changed by the statement.

Verify the Inserted Record

Use SELECT * to display every column and every row in the table:

SELECT * FROM testtable;

SELECT * means that all columns are returned from the selected table. The query output should include headings for name, surname, and year, followed by a line containing Amy, Goodridge, and 1991.

The headings correspond to the table's columns. Each displayed data line represents one row, or record. Seeing the new line confirms that the INSERT created the record in testtable.

INSERT Without a Column List

MySQL also supports a shorter form:

INSERT INTO table_name
VALUES (value1, value2, ...);

When the column list is omitted, values must be supplied for every table column in the exact order defined by the table. For the sample table, the definition order is assumed to be name, surname, then year.

This statement adds a second complete row:

INSERT INTO testtable
VALUES ('Mark', 'Smith', 1955);

Because the values follow the table's column-definition order, MySQL maps them as follows:

'Mark' goes to name.
'Smith' goes to surname.
1955 goes to year.

Verify both records with:

SELECT * FROM testtable;

The result should contain the earlier Amy Goodridge row and the Mark Smith row. The order of rows in a query result should not be assumed unless an ORDER BY clause is used.

Explicit Columns Versus Omitted Columns

Form	Requirement	Main benefit or risk
INSERT with named columns	Values must match the listed columns and their order.	Clear mapping and less dependence on the table's definition order.
INSERT without named columns	Values are required for every column in table-definition order.	Shorter syntax, but it depends on knowing the schema order.

Naming columns is the safer and clearer practice. It documents where each value belongs and makes the statement less dependent on the current table definition order. An INSERT without column names can fail when the number of values is wrong, or can place values in unintended columns when the assumed order is incorrect.

Data Values and Data Types

Each inserted value must be compatible with its target column's data type and constraints. In the sample:

name and surname receive quoted text values such as 'Amy' and 'Goodridge'.
year receives an integer such as 1991.

An incompatible value can produce an error or an unwanted conversion, depending on the column definition and MySQL settings. For example, a nonnumeric text value may not be valid for an integer column. Check the table structure and provide a value that matches the target column.

Insert Multiple Rows

One INSERT INTO statement can add several rows. Use one parenthesized value set for each row, separating the sets with commas:

INSERT INTO testtable (name, surname, year)
VALUES
    ('Lena', 'Brown', 1988),
    ('Omar', 'Khan', 1994);

Each parenthesized set represents one new record. Every set must contain values aligned with the same column list:

The first value in each set belongs to name.
The second value belongs to surname.
The third value belongs to year.

Run the following query to inspect the table after the multi-row insert:

SELECT * FROM testtable;

The result should include the previously inserted records as well as Lena Brown and Omar Khan.

Troubleshooting INSERT Statements

Column and value counts do not match

If MySQL reports that the number of supplied values is wrong, check for a missing or extra value. An explicit list such as (name, surname, year) requires exactly three corresponding values.

INSERT INTO testtable (name, surname, year)
VALUES ('Amy', 'Goodridge');

This fails because the statement names three columns but supplies only two values.

Values use the wrong order

An INSERT without a column list relies on the actual schema order. If that order differs from your assumption, the statement may fail or values may be assigned to the wrong columns. Use an explicit column list whenever possible.

A value is rejected

Check the target column's data type and constraints. Use quoted text for text columns and a suitable number for an integer column. A required column, unique value, or other constraint can also prevent insertion.

The new row is not visible

Confirm that the INSERT and SELECT use the same table and database. Then run:

SELECT * FROM testtable;

A common cause is targeting a different table or database than the one being inspected. Basic database access and table selection are covered in Access MySQL and Query a database.

There is a syntax error

Check the table name, parentheses, commas, quotes, and final semicolon. The essential structure is:

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

Key Points

INSERT INTO creates new rows in a table.
VALUES supplies the data for those rows.
With an explicit column list, values map to columns by position.
The number and order of listed columns and values must match.
Without a column list, provide a value for every column in exact table-definition order.
Use SELECT * FROM table_name; to verify the inserted records.
For reliable SQL, prefer naming the destination columns explicitly.

After learning to insert records, the next related operations are updating existing fields and removing rows.