MySQL INSERT Statement – Inserting Rows Into a Table

The article covers the basic syntax of the MySQL INSERT statement and explains how to use the INSERT command in the MySQL table using dbForge Studio for MySQL.

Introduction to a MySQL INSERT Statement

The INSERT statement is used to add data to a table. The INSERT INTO command inserts one or multiple rows into a MySQL table. Depending on the number of rows you want to add, the syntax slightly differs.

 

MySQL INSERT INTO – General Syntax

When inserting a single row into the MySQL table, the syntax is as follows:

INSERT INTO table_name(column_1,column_2,column_3) 
VALUES (value_1,value_2,value_3); 

In the INSERT INTO query, you should specify the following information:

• table_name: A MySQL table to which you want to add a new row.
• (column_1,column_2,column_3): A list of columns the new row will contain. The columns are separated by a comma in round brackets.
• (value_1,value_2,value_3): A list of values separated by a comma in round brackets that will be used for each corresponding column.

The number of columns and values should match; otherwise, it fails.

When inserting multiple rows into a MySQL table, the syntax is as follows:

INSERT INTO table_name (column_1, column_2, column_3, ...) 
VALUES 
      (value_list_1), 
      (value_list_2), 
      ...
      (value_list_n); 

Here you should enter the following information:

• table_name: A table into which you want to insert data.
• column_1, column_2, column_3: A list of columns to store data. The columns are separated by a comma in round brackets.
• value_list_1/2/.../n: A list of values to be inserted in one row. The values are separated by a comma in round brackets. The number of values in each list should match the number of columns specified in (column_1, column_2, column_3, ...).

In addition, rows are separated by commas in the VALUES clause.

MySQL INSERT Examples

Let’s explore some examples of using a MySQL INSERT statement when performing data-related tasks.

To start with, create three tables: Customers, Products01, and Smartphones by executing the following script:

CREATE TABLE Customers
(
  ID int,
  Age int,
  FirstName varchar(20),
  LastName varchar(20)
);

-- Creating the Products table

CREATE TABLE Products01
(
  ID int,
  ProductName varchar(30) NOT NULL,
  Manufacturer varchar(20) NOT NULL,
  Price decimal NOT NULL,
  ProductCount int DEFAULT 0
);

CREATE TABLE Smartphones
(
  ID int,
  ProductName varchar(30) NOT NULL,
  Manufacturer varchar(20) NOT NULL,
  Price decimal NOT NULL,
  ProductCount int DEFAULT 0
);

MySQL INSERT command used to insert specified values

This command can be used when it is necessary to add values and columns for the specified rows. For example, add data only for the FirstName and LastName columns of the Customers table.

INSERT INTO Customers(ID, FirstName, LastName)
VALUES ('1', 'User', 'Test');

The command inserts values in the FirstName and LastName columns of the Customers MySQL table. Since the Age column uses Null as a default value, MySQL inserts Null into the Age column if you do not specify explicitly its value in the INSERT statement.

MySQL INSERT command used to insert values to all columns

This command can be used in order to add values to all columns. For example, add values to the Product01 table. Keep in mind that the order of values should match the order of columns in the table.

INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

The statement inserts the specified values into all columns of the Product01 table.

MySQL INSERT command used to insert the default value

This command can be used when the default value is specified for the column. For example, the default value for the ProductCount column of the Products01 table equals 0. To insert the default value, execute either of the following queries:

INSERT INTO Products01(ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HD Global', '41000', DEFAULT);

INSERT INTO Products01 (ProductName, Manufacturer, Price, ProductCount)
VALUES ('Nokia 9', 'HD Global', '41000', NULL);

The statement inserts the default value for the ProductCount column.

MySQL INSERT used to insert a specific value

To insert values into the columns, we can use the SET clause instead of the VALUES clause.

INSERT INTO Customers SET ID=2, FirstName='User2';

In the output, the statement inserts values into the columns based on the explicitly specified values in the SET clause.

MySQL INSERT used to insert data from another table

Using the SELECT clause in the MySQL INSERT INTO statement allows inserting the rows generated by the SELECT statement into the target MySQL table.

INSERT INTO Products01 SELECT * FROM Smartphones;

In this case, the INSERT INTO statement copies the rows you retrieved with the SELECT statement from the Smartphones table and inserts them into the Products01 table.

If you want to retrieve specific rows based on the condition, apply filtering options in the WHERE clause. Keep in mind that the structure of both tables should match; otherwise, it won’t work.

INSERT INTO Products01 SELECT * FROM Smartphones WHERE Price=34000;

This command adds to the Products01 table values from the Smartphones table where the price equals 34000.

You can also copy and insert specified columns from another table. However, in this case, make sure that there is no record with the specified name in the target table; otherwise, it fails, and the error occurs.

MySQL INSERT – ignore errors when inserting data

In case you face errors or any unexpected behavior while executing the insertion of rows into the MySQL table, the statement execution is interrupted. Rows with valid and invalid values are not added to the table. For example, execute the following query:

INSERT INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

This statement returns the error because the record with this ID already exists. Now, modify the statement with IGNORE. It allows you to insert rows with valid values but overpass the rows with invalid values.

INSERT IGNORE INTO Products01
VALUES ('01', 'iPhoneX', 'Apple', 35000, 3);

As a result, the script was executed without errors. MySQL will notify you that rows were added to the table.

Creating the INSERT INTO Query with dbForge Studio for MySQL

All the data manipulations, including the Create, Select, Insert, Update, Delete, or Drop operations can be easily performed with dbForge Studio for MySQL. The tool offers a rich set of advanced capabilities and features to boost your productivity and save your time when developing, managing, and deploying MySQL databases in the easy-to-use interface.

Assume we need to add a new language to the database (we use the popular MySQL sample database sakila to illustrate the work of dbForge Studio for MySQL).

In dbForge Studio for MySQL, we can use the Generate Script As feature to insert one or multiple rows in the MySQL table.

To insert information about customers, do the following:

1. In Database Explorer, right-click the required table and select Generate Script As > INSERT > To New SQL Window.

2. The SQL code editor opens containing the automatically generated script. Insert data for the specified column (name) into the VALUES clause.

3. On the standard toolbar, click Execute. The statement adds the new language (Portuguese) to the bottom line of the grid.

Conclusion

In the article, we have reviewed a MySQL INSERT statement and examples, when the command can be used, and provided a particular example of how to work with the MySQL INSERT queries in dbForge Studio for MySQL.

Download a free 30-day trial version of dbForge Studio for MySQL to evaluate all the useful features the tool provides.