How to Insert Data into Tables with the INSERT Statement in MySQL

MySQL is one of the most widely used relational database systems available. One of the fundamental operations performed using MySQL is inserting data into tables. This article will provide an overview of the INSERT statement, which allows you to add new rows of data into a table.

Basic Syntax of the INSERT Statement

First, the INSERT statement requires that the table you are inserting data into already exists. The basic syntax of the INSERT statement is:

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

The first row requires you to specify which columns the data is being inserted into. These columns must exist in the target table. The values must also match the data types of the corresponding tables.

Inserting Data into All Columns

If you are adding a complete new row into the table using all of the columns, you can omit the column names. However, it is critical to ensure that the values are provided in the same order as the columns are defined in the table.

For example, both of the following code snippets are valid and work to insert data into a price database.

INSERT INTO price_table (product_name, price, quantity)
VALUES (‘Notebook’, 10.99, 150);

INSERT INTO price_table
VALUES (‘Notebook’, 10.99, 150);

Inserting Data into Specific Columns

If you want to skip certain columns, you must specify which specific columns you are inserting data into in your INSERT INTO line. For example, if you do not know the price but want to add the product name and quantity, you can use the following code snippet.

INSERT INTO price_table (product_name, quantity)
VALUES (‘Notebook’, 150);

Inserting Multiple Rows

If you have data for multiple rows you want to add at once, you can use a single INSERT statement to simplify your code and improve performance. Each row of data should be entered separately, encased in parenthesis, and separated by commas.

For example, to enter the pricing information for two products, you can do the following.

INSERT INTO price_table
VALUES
	(‘Notebook’, 10.99, 150), 
	(‘Pencil’, 2.49, 300);

As a note, if an error occurs when inserting any of the multiple rows, none of the rows will be inserted, depending on the specific MySQL configuration.

INSERT SELECT Statement

The INSERT SELECT statement allows you to insert data into one table by selecting data from another table. This is useful when transferring data to minimize data entry errors.

For example, if there is a sales table that has the name and price of various products, this information can be inserted into the price table directly. Optional conditions can be included as well using where statements.

INSERT INTO price_table 
SELECT product_name, price, quantity
FROM sales_table
WHERE price > 5;

This will import the product name, price, and quantity of all rows in the sales table where the price is greater than $5 into the price table.

Troubleshooting

Some common errors that may arise when using the INSERT statement are:

- Duplicate entry error: this occurs when your table has a unique column constraint, like a primary key, and you have attempted to insert a duplicate value. You should either change the data you are inserting, or use the INSERT IGNORE statement to ignore these duplicated values. Alternatively, you can use REPLACE INTO instead of INSERT to replace the original row of data.
- Data type mismatch: this occurs if the data you are entering does not match the data type of the target column, such as entering text into a numeric column.
- Column count mismatch: this occurs when the number of data pieces you have entered on your VALUES exceeds the number of columns in the target table. This is most likely to occur if you do not specify the column names in your INSERT INTO line.