This tutorial explains how to use the MySQL INSERT statement with syntax and examples.
The MySQL INSERT statement is used to insert a single record or multiple records into a table in MySQL.
In its simplest form, the syntax for the INSERT statement when inserting a single record using the VALUES keyword in MySQL is:
However, the full syntax for the INSERT statement when inserting a single record using the VALUES keyword is:
In its simplest form, the syntax for the INSERT statement when inserting multiple records using a sub-select in MySQL is:
However, the full syntax for the INSERT statement when inserting multiple records using a sub-select is:
Parameters or Arguments
Optional. The insert will be delayed until there are no processes reading from the table.
Optional. The inserted rows are put in a buffer until the table is available and the next SQL statement can be issued by the process.
Optional. The insert will be given a higher priority overriding the database's "insert" priorities.
Optional. If specified, all errors encountered during the insert are ignored and treated instead as warnings.
The table to insert the records into.
The columns in the table to insert values.
The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
The source table when inserting data from another table.
Optional. The conditions that must be met for the records to be inserted.
ON DUPLICATE KEY UPDATE
Optional. If specified and a row is inserted that would violate a primary key or unique index, an update will be performed instead of an insert. dup_column1 would be assigned the value of dup_expression1, dup_column2 would be assigned the value of dup_expression2, and so on.
When inserting records into a table using the MySQL INSERT statement, you must provide a value for every NOT NULL column.
You can omit a column from the MySQL INSERT statement if the column allows NULL values.
Example - Using VALUES keyword
The simplest way to create a MySQL INSERT query to list the values using the VALUES keyword.
This MySQL INSERT statement would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 1000 and a supplier_name of 'Dell'.
Example - Using sub-select
You can also create more complicated MySQL INSERT statements using sub-selects.
By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.
With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by calling the mysql_info function or by running the following MySQL SELECT statement before performing the insert.