MySQL - INSERT - Guide, Examples and Alternatives

INSERT statement inserts one or more rows into an existing table. You can specify values explicitly, or select from another table.

Quick Example:

  -- Create a table
  CREATE TABLE states (abbr CHAR(2), name VARCHAR(30));
 
  -- Insert 3 rows
  INSERT INTO states VALUES ('CA', 'California'), ('TX', 'Texas'), ('WA', 'Washington');

Overview:

Syntax (full...)	INSERT [IGNORE] [INTO] table_name [(column, …)] VALUES (value | DEFAULT, …) [, (…), …] [ON DUPLICATE KEY UPDATE column=value, …]
	INSERT [IGNORE] [INTO] table_name [(column, …)] SELECT … [ON DUPLICATE KEY UPDATE column=value, …]
Multiple Rows Insert
Merge Capability
IGNORE Option	Rows violating primary or unique constraint are skipped
DEFAULT Keyword	Specifies to insert the default value for a column. Another way is to skip the column from column list
Default in All Columns	INSERT INTO table_name VALUES();
Number of Rows	ROW_COUNT() returns the number of inserted rows
Auto-increment	If table has auto-increment column, LAST_INSERT_ID() returns the value for the first inserted row

Version: MySQL 5.6

You can insert multiple rows in a single INSERT statement:

   CREATE TABLE cities 
   (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(70),
      country VARCHAR(70) DEFAULT 'Unknown'
   );
 
   -- Insert 2 rows
   INSERT INTO cities VALUES 
     (NULL, 'Reading', 'United Kingdom'),        -- id 1
     (NULL, 'Manchester', 'United Kingdom');  -- id 2

Default Values:

There are various ways to specify the default values:

   -- Insert default values in all columns
   INSERT INTO cities VALUES();   -- id 3
 
   -- Auto-increment will be set by default   
   INSERT INTO cities (name, country) VALUES ('San Diego', 'United States');   -- id 4
 
   -- Auto-increment and country set by default
   INSERT INTO cities (name) VALUES ('York');  -- id 5

Table content:

Ignore Duplicates:

Let's try to insert multiple rows containing duplicates using IGNORE options:

   INSERT IGNORE INTO cities VALUES
     (5, 'San Jose', 'United States'),          -- id 5 already exists, this row will be skipped
     (NULL, 'Lyon', 'France'),                    -- id 6 will be assigned
     (NULL, 'Warsaw', 'Poland');               -- id 7

Note that LAST_INSERT_ID function returns ID value for the first successfully inserted row:

   SELECT LAST_INSERT_ID();
   -- Returns: 6

Table content:

Merge:

Insert a row if it does not exist, otherwise update it:

  INSERT INTO cities VALUES (5, 'San Jose', 'United States')
    ON DUPLICATE KEY UPDATE name = 'San Jose', country = 'United States';

Table content:

SQL INSERT statement in other databases:

PostgreSQL:

Multiple Rows Insert
DEFAULT Keyword
Insert Default to All Columns	INSERT INTO table_name DEFAULT VALUES;
Ignore Duplicates
Update Duplicates

• MySQL Articles and Reference
• INSERT Statement - MySQL 5.6 Reference Manual