SQL INSERT Command in MySQL

The SQL INSERT command adds new rows to a table. The recommended form names the columns explicitly and supplies values in the same order.

INSERT INTO student (name,class,mark,gender)
VALUES ('Alex','Four',78,'male');

Here name, class, mark and gender are the destination columns. The values must match those columns in sequence and satisfy the table's datatype and constraint rules.

SQL INSERT Syntax Top ↑

The common syntax is:

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

The number and order of values should correspond to the columns listed after the table name.

Text and date values are normally quoted, while numeric values are normally written without quotes:

INSERT INTO student (name,class,mark)
VALUES ('John','Three',75);

Insert One Row Top ↑

To add one student record:

INSERT INTO student (name,class,mark,gender)
VALUES ('Maria','Five',82,'female');

If the query succeeds, one new row is added to the table.

Check the inserted row Top ↑

SELECT id,name,class,mark,gender
FROM student
WHERE name='Maria';

Use the SELECT tutorial and WHERE tutorial for retrieving and filtering records.

Insert Multiple Rows with One Statement Top ↑

MySQL allows several rows to be added with one INSERT statement:

INSERT INTO student (name,class,mark,gender)
VALUES
('Ravi','Four',76,'male'),
('Sara','Four',88,'female'),
('Mohan','Five',69,'male');

This is clearer and usually more efficient than sending three separate INSERT statements when all rows are known together.

Why Specify Column Names? Top ↑

SQL also permits an INSERT without a column list when a value is supplied for every column in the table's expected order:

INSERT INTO t1
VALUES (1,'one');

However, explicitly naming the columns is safer and easier to maintain:

INSERT INTO t1 (id,name1)
VALUES (1,'one');

If a table later gains another column with a default value, an INSERT that explicitly names the columns it uses is less likely to require changes.

Modern sample table Top ↑

The older version of this tutorial used legacy MySQL syntax such as TYPE=MyISAM and integer display widths. A simple modern example can be written as:

CREATE TABLE t1 (
    id INT NOT NULL,
    name1 VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
);

Now add several rows:

INSERT INTO t1 (id,name1)
VALUES
(1,'one'),
(2,'two'),
(3,'three');

INSERT with an AUTO_INCREMENT Column Top ↑

When an ID column uses AUTO_INCREMENT, normally omit that column from the INSERT and let MySQL generate the value.

CREATE TABLE members (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO members (name)
VALUES ('Alex');

MySQL supplies the next generated id. See MySQL AUTO_INCREMENT for details.

Insert NULL and DEFAULT Values Top ↑

If a column allows SQL NULL, insert the SQL keyword NULL without quotes:

INSERT INTO student (name,class,mark,gender)
VALUES ('Sam','Four',NULL,'male');

NULL is different from an empty string such as ''.

If a column has a defined default value, it can be omitted from the column list:

INSERT INTO members (name)
VALUES ('Nina');

or the DEFAULT keyword can be used where appropriate:

INSERT INTO members (name,status)
VALUES ('Nina',DEFAULT);

See SQL NULL values for the difference between NULL and ordinary values.

Copy Rows with INSERT ... SELECT Top ↑

INSERT can add rows returned by a SELECT query. Explicitly list the destination and source columns so their purpose is clear:

INSERT INTO student2 (id,name,class,mark,gender)
SELECT id,name,class,mark,gender
FROM student;

The selected values must be compatible with the corresponding destination columns.

Copy Only Matching Rows Top ↑

Add a WHERE condition to copy only selected rows:

INSERT INTO student2 (id,name,class,mark,gender)
SELECT id,name,class,mark,gender
FROM student
WHERE class='Four';

Only students from class Four are returned by SELECT and inserted into student2.

Copy one column between tables Top ↑

INSERT INTO table1 (column1)
SELECT column1
FROM table2;

For more table-copy examples, see copy data to a new table and copy data to an existing table.

Common SQL INSERT Errors Top ↑

Column and value counts do not match Top ↑

This is invalid because three columns are listed but only two values are supplied:

-- Incorrect
INSERT INTO student (name,class,mark)
VALUES ('Alex','Four');

Wrong datatype Top ↑

A value must be compatible with the destination column. For example, a text value should not be inserted into a numeric column when the database cannot convert it appropriately.

NOT NULL column has no value Top ↑

If a required NOT NULL column has no default, the INSERT must provide an acceptable value.

Duplicate key value Top ↑

An INSERT can fail when it tries to create a value that violates a PRIMARY KEY or UNIQUE constraint.

INSERT INTO t1 (id,name1)
VALUES (1,'another');

If id=1 already exists and id is the primary key, MySQL reports a duplicate-key error.

Unescaped application input Top ↑

When INSERT values come from PHP users or forms, do not construct SQL by concatenating raw input. Use a prepared statement in the application layer.

INSERT Records from PHP PDO Top ↑

The SQL tutorial explains INSERT itself. When values come from PHP, use a PDO prepared statement with placeholders:

INSERT INTO student (name,class,mark)
VALUES (:name,:class,:mark);

The placeholders are bound to application values by PDO rather than manually placing user input into the SQL string.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: