MySQL Insert Into Table – Insert Statement Syntax & Examples

This Tutorial Explains the MYSQL INSERT INTO Table Statement Along with Query Syntax & Examples. Also, Learn Different Variations of MYSQL Insert Command:

In MySQL, INSERT command is used to add data to the table. Using this command, we can Insert data in one or more than one row in one single transaction. Also, data can be added to one or more than one table in a single transaction.

We will go through all these in the upcoming sections. Before proceeding ahead, please note, that we are using MySQL version 8.0. You can download it from here.

=> Click here for the complete MySQL tutorial series

MySQL INSERT Command Syntax

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE] 
INTO tablename (column1, column2, column3...)
VALUES (value1, value2, value3,                      ....);

Syntax Explanation:

• The syntax starts with the keyword “INSERT INTO”, thereby informing the MySQL Server about the type of activity to be performed. This is a mandatory keyword and cannot be omitted.
• Next comes the name of the table on which the insert action has to be performed. This is mandatory and cannot be omitted too.
• Next, will be the column names to be inserted along with their corresponding values. Again, this also is mandatory and cannot be omitted.
• Next, will be the values clause. In this clause, one has to provide the value for each and every column that we are inserting into the table. The sequence of values and the sequence of column names should be in sync.
• The number and data types of columns should be the same as that of the values.

Modifiers In INSERT Statement

• LOW_PRIORITY: This modifier informs the MySQL Engine to delay the execution of the INSERT statement until such a time that there are no connections with reading from the table that we are attempting to INSERT. This helps in achieving consistency across all other operations that will be performed on that table.
• HIGH_PRIORITY: This modifier informs the MySQL Engine to give high priority to the INSERT statement over any other statement/transaction that is being performed on the table.
• IGNORE: This modifier informs MySQL Engine to ignore any errors that may arise due to the execution of the INSERT statement. Any errors that arise would be treated as mere warnings and insertion of records into the table would proceed unhindered.
• DELAYED: This is the MySQL extension to standard SQL. When INSERT DELAYED is issued by the user, the server queues all the rows and the data is inserted in the table at a later time, when the table is not in use by any other transactions.

MySQL INSERT Example

The following is a sample table created in MySQL.

Schema Name: pacific
Table Name: employees
Column Names:

• empNum – Holds integer values for the employee number.
• lastName – Holds varchar values for the last name of the employee.
• firstName – Holds varchar values for the first name of the employee.
• email – Holds varchar values for the email ID of the employee.
• deptNum – Holds varchar for the department ID that an employee belongs to.
• salary – Holds decimal values of the salary for each employee.
• start_date – Holds date values for the joining date of the employee.

Schema Name: pacific
Table Name: employees_history
Column Names:

• empNum – Holds integer values for the employee number.
• lastName – Holds varchar values for the last name of the employee.
• firstName – Holds varchar values for the first name of the employee.
• email – Holds varchar values for the email ID of the employee.
• deptNum – Holds varchar for the department ID that an employee belongs to.
• salary – Holds decimal values of salary for each employee.
• start_date – Holds date values for the joining date of the employee.

MySQL INSERT Statement Variations

#1) MySQL Insert A Single Row

First, we will have a look at a scenario where we have specified both the column names and the values to be inserted using the INSERT INTO keyword.

For Example, Here, we will try to insert a new employee. We will add the employee number, first and last name, along with that we will also update the email ID, salary and department id that the new employee should belong to.

The query and the corresponding results are as follows:

As depicted in the picture above, the INSERT statement has executed successfully and inserted one row into the employee’s table.

The output statement below shows the time at which the statement was executed, the MySQL statement that was executed and the number of rows that were affected.

Please note here, that the value for each column is mentioned in the same order as that of the column names. Also, observe that the column with the data type of integer/decimal is not enclosed within inverted commas, however, the column types with varchar/char data type have been enclosed with inverted commas.

In order to verify the output of this INSERT statement, let’s execute the SELECT statement on this table with the empNum as 1012.

Query:

INSERT INTO employees (
	empNum, lastName, firstName, email, deptNum, salary
    )
    VALUES (
    1012, 'Luther', 'Martin', 'ml@gmail.com', 3, 13000
    )
    ;

Table Snapshot After:

#2) MySQL Inserting Data Only In Specified Column

Next, is another way of inserting data into a table, but by inserting records only in the required columns and not in all the columns. However, please note that we cannot omit the key columns in this scenario. In the case of our employee’s table, the key column being the empNum column. Let’s try this out.

For Example, We will insert a new record in the employee table with data on just empNum, lastName, firstName. We will not assign any email ID, department or salary for this employee.

Following is the query and its result:

As depicted in the picture above, the insert statement has executed successfully and inserted one row into the employee’s table.

Please note, that in order to insert only the selected columns, the columns that we skipped should either be declared NULL or should have some default value which would be populated in case that column is skipped. If this condition is not satisfied, then the insert statement would fail.

Let’s verify the execution of the above INSERT statement by executing a SELECT statement for empNum = 1013.

Query:

INSERT INTO employees (
	empNum, lastName, firstName
    )
    VALUES (
    1013, 'Nolan', 'Chris'
    )
;

Table Snapshot After:

#3) MySQL Insert Multiple Rows

Next, we will go through the scenario where we have to insert multiple rows into the table with the same INSERT statement.

For Example, in this case, we need to mention the column names only once, but we can keep repeating the values for those columns as many times as required.

Following is the query along with the results associated with it:

As depicted in the picture above, the execution of the statement was successful.

Do observe the message part, which states that 3 rows were affected, this implies that this single INSERT statement inserted 3 records with the execution of this INSERT statement.

Further reading =>> MySQL If Statement  Tutorial

Let’s verify the output of our INSERT statement by executing the SELECT statement for new employee IDs 1014, 1015, and 1016.

The details are as follows:

Query:

INSERT INTO employees (
	empNum, lastName, firstName, email, deptNum, salary
    )
    VALUES 
    (1014, 'Murray', 'Keith', 'km@gmail.com', 1, 25000),
    (1015, 'Branson', 'John', 'jb@gmail.com', 2, 15000),
    (1016, 'Martin', 'Richard', 'rm@gmail.com', 4, 5000)
  ;

Table Snapshot After:

#4) MySQL Inserting Date

Next, we will go through the scenario where we have to insert values to the date column.

For Example, Inserting values to the date column could be tricky. The date in MySQL could be added to the ‘YYYY-MM-DD’ format. In order to achieve this, let us add a column start_date with the default value as ‘0001-01-01’.

This implies that all the existing records in the employee’s table with the start_date will be updated as ‘0001-01-01’. The alter statement will be as follows.

Query:

ALTER TABLE employees
        ADD start_date DATE default '0001-01-01'
      ;

Let’s verify the output of the above query by executing a simple SELECT statement on the table:

So, we have added a new date column with data type as “DATE” with the default value as ‘0001-01-01’. Now let’s insert two new employee records, one with the current date and another with a particular date.

Following are the queries along with the details:

As shown in the above picture, we have used the feature of inserting multiple rows in the table as explained in the previous section.

The first record was inserted with the CURRENT_DATE() function. This function returns the current system date. The second record was inserted with a particular date in the ‘YYYY-MM-DD’ format.

Next, we will verify the output of our INSERT statement with a SELECT statement for empNum 1017 and 1018.

The first record, with empNum=1017, has the start_date the same as the current date which is 25^th Nov 2019 (in this case the date on which this tutorial was written)  in ‘YYYY-MM-DD’ format.

Query:

INSERT INTO employees (
	empNum, lastName, firstName, email, deptNum, salary, start_date
    )
    VALUES 
    (1017, 'Johnson', 'Eve', 'ej@gmail.com', 3, 5500, CURRENT_DATE()),
    (1018, 'Bond', 'Nolan', 'nb@gmail.com', 2, 15000, '2019-09-13') 
    ;

Table Snapshot After:

#5) MySQL Insert Into A Table From Another Table

Next, we will go through the scenario where we have to insert data in a new table from an existing table.

For Example, Consider a scenario where we have to periodically move data from our existing table to a historic or archive table. In order to achieve this, let’s create a new table employee_history.

Our task is to move data from the employee table to the employee_history table.

The CREATE statement is as follows:

Query:

CREATE TABLE employees_history LIKE employees
      ;

Let’s verify the output of the above query by executing a simple DESC statement on the new table which will give us the table structure of the new table:

So, we have created a new table. Now let’s load data into this new table from the employee’s table.

=>Learn more about MySQL CREATE TABLE COMMAND.

The following are the query and its details:

As shown in the above picture, the insertion of data in the new table from the existing table was successful.

Please observe, the message column in the output tab. It says 18 rows affected. This implies that all the 18 rows in the existing table were copied to the newly created employees_history table.

Next, we will verify the output of our INSERT statement with a SELECT statement on the employees_history table.

The above picture depicts all the rows copied from the employee’s table in the employees_history table.

The above picture depicts all the rows copied from the employees table in the employees_history table.

Query:

INSERT INTO employees_history (
	empNum, lastName, firstName, email, deptNum, salary, start_date
    )
    SELECT
		empNum, lastName, firstName, email, deptNum, salary, start_date
	 FROM employees
     ;

Table Snapshot After:

Frequently Asked Questions And Answers

Q #1) How do you insert data into the table in MySQL?

Answer: To insert data into a MySQL table, MySQL provides us with an important keyword of “INSERT INTO”. This is followed by the table name, list of columns and list of corresponding values that need to be inserted. We can use this keyword to insert one or more columns in the same transaction.

Q #2) Explain the clause “INSERT IGNORE INTO”.

Answer: When we attempt to insert more than one row in a single INSERT statement, it might be possible that the execution might fail due to bad data for any particular row. This will cause the transaction to stop and be rolled back to the previous commit.

In order to avoid such scenarios, “INSERT IGNORE INTO” is used. This clause ignores the error causing invalid records and inserts only valid records so that the transaction does not stop. This is helpful where you have to do bulk inserts and you cannot afford to redo it for any bad data for a row.

Q #3) How do I update attributes in MySQL?

Answer: We can update attribute(s) using the MySQL Update statement, with the statement beginning with the UPDATE keyword followed by the table name. Next is the SET clause followed by a column name and a WHERE clause.

Q #4) Can I insert multiple records in a single MySQL Insert transaction?

Answer: As depicted above, under the “Inserting Data in multiple rows” section, we can insert multiple rows in a single insert transaction. We can use the INSERT INTO clause followed by the table name and list of columns, but instead of one list of values, we need to mention multiple lists of values for each of the columns.

Q #5) Where do you download MySQL from?

Answer: You can download MySQL version 8.0 from here: MySQL.

Also Read => How to Download MySQL

Q #6) Can the SELECT clause be used in MySQL INSERT statement?

Answer: Yes, SELECT can be used along with the MySQL INSERT statement. This has been explained in the section, “MySQL Inserting a table from another table”. This is used when we have to insert rows in one table, using the data already present in some other table.

For Example, moving data from the current table to the historic table in the data warehouse.

Q #7) How does MySQL store date in tables?

Answer: MySQL uses the “YYYY-MM-DD” format for storing date in tables. The data type here is DATE. The range of dates that are supported by MySQL is from ‘1000-01-01’ to ‘9999-12-31’.

MySQL also has a DATETIME datatype to store both date and time. The format is “YYYY-MM-DD hh:mm: ss”. The acceptable range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

Recommended Reading =>> MySQL Data Types Tutorial

Conclusion

Thus, in this tutorial, we learned about the five different ways of executing INSERT statements in MySQL.

1. MySQL Inserting Single Row
2. MySQL Inserting Data only in Specified Column
3. MySQL Inserting Data in multiple Rows
4. MySQL Inserting Date
5. MySQL Inserting a table from another table

We can use either of these, based on our project requirement.

Happy Reading!!