Inserting Data into A Table Using MySQL INSERT Statement?

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 one or more rows into a table. The following illustrates the syntax of the INSERT statement:

1 2	INSERT INTO table(column1,column2...) VALUES (value1,value2,...);

First, you specify the table name and a list of comma-separated columns inside parentheses after the INSERT INTO clause.

Then, you put a comma-separated values of the corresponding columns inside the parentheses followed the VALUES keyword.

You need to have an INSERT privilege to use the INSERT statement.

Let’s create a new table named tasks for practicing the INSERT statement.

1 2 3 4 5 6 7 8

CREATE TABLE IF NOT EXISTS tasks (     task_id INT(11) 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:

1 2	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 inform the number of rows affected. In this case, one row were affected.

1 2 3 4

SELECT     * FROM     tasks;

MySQL INSERT – insert multiple rows

In order to insert multiple rows into a table, you use the INSERT statement with the following syntax:

1 2 3 4

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

In this form, the value list of each row is separated by a comma. For example, to insert multiple rows into the tasks table, you use the following statement:

1 2 3 4

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 value of the corresponding column for all columns in the table, you can ignore the column list in the INSERT statement as follows:

1 2	INSERT INTO table VALUES (value1,value2,...);

and

1 2 3 4

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 value for the auto-increment column automatically.

MySQL INSERT with SELECT clause

In MySQL, you can specify the values for the INSERT statement from a SELECT statement. This feature is very handy because you can copy a table fully or partially using the INSERT and SELECT clauses as follows:

1 2	INSERT INTO table_1 SELECT c1, c2, FROM table_2;

Let’s copy the tasks table to the task_1 table.

First, create a new table named tasks_1 by copying the structure of the tasks table as follows:

1	CREATE TABLE tasks_1 LIKE tasks;

Second, insert data from the tasks table into the tasks_1  table using the following INSERT statement:

1 2	INSERT INTO tasks_1 SELECT * FROM tasks;

Third, check the tasks_1 table to see if we actually copy it from the tasks table.

1 2 3 4

SELECT     * FROM     tasks_1;

MySQL INSERT with ON DUPLICATE KEY UPDATE

If the new row violates the PRIMARY KEY or UNIQUE constraint, MySQL issues an error. For example, if you execute the following statement:

1 2	INSERT INTO tasks(task_id,subject,start_date,end_date,description) VALUES (4,'Test ON DUPLICATE KEY UPDATE','2010-01-01','2010-01-02','Next Priority');

MySQL issues an error message:

1	Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec

Because the row with the primary key task_id 4 already exists in the tasks table, the statement violates the PRIMARY KEY constraint.

However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will insert a new row or update the existing row with the new values.

For example, the following statement updates the existing row whose task_id is 4 with the new task_id and subject.

1 2 3 4 5

INSERT INTO tasks(task_id,subject,start_date,end_date,description) VALUES (4,'Test ON DUPLICATE KEY UPDATE','2010-01-01','2010-01-02','Next Priority') ON DUPLICATE KEY UPDATE    task_id = task_id + 1,    subject = 'Test ON DUPLICATE KEY UPDATE';

MySQL issues a message saying that  2 rows affected .

Let’s check the tasks table:

1 2 3 4

SELECT     * FROM     tasks;

The new row was not inserted. But the existing row with the task_id 4 was updated.

The INSERT ON DUPLICATE KEY UPDATE statement above is equivalent to the following UPDATEstatement:

1 2 3 4 5 6

UPDATE tasks SET     task_id = task_id + 1,     subject = 'Test ON DUPLICATE KEY UPDATE' WHERE     task_id = 4;

For more information on the INSERT ON DUPLICATE KEY UPDATE statement, please check it out the MySQL insert or update tutorial.

In this tutorial, we have shown you how to use various forms of the MySQL INSERT statement to insert data into a table.