Summary: in this tutorial, you will learn how to use MySQL INSERT statement to insert data into the database tables.
Simple MySQL INSERT statement
The MySQL INSERT statement allows you to insert data into tables. The following illustrates the syntax of the INSERTstatement:
INSERT INTO table(column1,column2...) VALUES (value1,value2,...)
First, after the INSERT INTO, you specify the table name and a list of comma-separated columns inside parentheses. Then you put a comma-separated values of the corresponding columns inside parentheses followed the VALUESkeyword.
You need to have the INSERT privilege to use the INSERT statement.
Let’s create a new table named tasks for practicing the INSERT statement.
CREATE TABLE IF NOT EXISTS tasks ( task_id int(11) NOT NULL AUTO_INCREMENT, subject varchar(45) DEFAULT NULL, start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL, description varchar(200) DEFAULT NULL, PRIMARY KEY (task_id) )
For example, if you want to insert a new task into the tasks table, you use the INSERT statement as follows:
INSERT INTO tasks(subject,start_date,end_date,description) VALUES('Learn MySQL INSERT','2010-01-01','2010-01-02','Start learning..')After executing the statement, MySQL returns a message to indicate how many rows were affected. In this case, one row were affected.
MySQL INSERT – insert multiple rows
In order to insert multiple rows into a table, you use the INSERT statement with the following syntax:
INSERT INTO table(column1,column2...) VALUES (value1,value2,...), (value1,value2,...), ...
Each row, which you want to insert into the table, is specified by a comma-separated values inside parentheses. For example, to insert multiple tasks in the tasks table, you use the following query:
INSERT INTO tasks(subject,start_date,end_date,description) VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'), ('Task 2','2010-01-01','2010-01-02','Description 2'), ('Task 3','2010-01-01','2010-01-02','Description 3');3 rows affected. Great!
If you specify the values of the corresponding column for all columns in the table, you can ignore the column list in the INSERT statement as follows:
INSERT INTO table VALUES (value1,value2,...)
and
INSERT INTO table VALUES (value1,value2,...), (value1,value2,...), ...
Notice that you don’t have to specify the value for auto-increment column e.g., taskid column because MySQL generates its values automatically.
MySQL INSERT with SELECT clause
In MySQL, you can specify the values for the INSERT statement from a SELECT statement. It is very handy because you can clone a table fully or partially by using the INSERT and SELECT clauses as follows:
INSERT INTO table_1 SELECT c1, c2, FROM table_2;
First, we create a new table named tasks_1 by cloning the structure of the tasks table as follows:
CREATE TABLE tasks_1 LIKE tasks;
Second, we can insert data into the tasks_1 table from the tasks table by using INSERT statement:
INSERT INTO tasks_1 SELECT * FROM tasks;
Third, we can check the tasks_1 table to see if we actually clone it from the tasks table.
SELECT * FROM tasks_1;
MySQL INSERT with ON DUPLICATE KEY UPDATE
If the new record that you want to insert may cause duplicate value in PRIMARY KEY or a UNIQUE index, MySQL will issue an error. For example, if you execute the following statement:
INSERT INTO tasks(task_id,subject,start_date,end_date,description) VALUES (4,'Task 4','2010-01-01','2010-01-02','Description 4');
MySQL will issue an error message:
Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec
However if you specify ON DUPLICATE KEY UPDATE in the INSERT statement, MySQL will update the old record, for example:
INSERT INTO tasks(task_id,subject,start_date,end_date,description) VALUES (4,'Task 4','2010-01-01','2010-01-02','Description 4') ON DUPLICATE KEY UPDATE task_id = task_id + 1;
MySQL issues a message saying that 2 rows affected.
Let’s check the tasks table:
SELECT * FROM tasks;
A new record was inserted but the old record with task_id value 4 was updated. The above query is equivalent to the following query:
UPDATE tasks SET task_id = task_id+1 WHERE task_id = 1;
In conclusion, when you use ON DUPLICATE KEY UPDATE in the INSERT statement with the new record that causes the duplicate value in PRIMARY KEY or UNIQUE index, MySQL updates the old record and a new record.
In this tutorial, we have shown you how to use various forms of the MySQL INSERT statement to insert data into a database table.
Related Tutorials
PHP MySQL Insert Data Into a Table