In this tutorial we will see how to insert several rows of data in a
single query in MySQL server.
For this tutorial we will use MySQL Server with MySQL Workbench.
Sometimes, the situation can arise in which it is necessary that in a
single INSERT “n” rows must be inserted in SQL Server.
To add multiple rows to a table at once, use the following form of the
INSERT INTO table_name (column_list)
In this syntax, instead of using a single list of values, it uses
multiple comma-separated lists of values for the insert.
The number of rows that can be inserted at one time is limited with this
form of the INSERT statement.
You should probably use or consider using multiple INSERT statements,
BULK INSERT statements, or a derived table, to insert more rows.
Note that this multi-row INSERT syntax is only supported by SQL Server
2008 or later.
To insert multiple rows returned from a SELECT statement, use the INSERT
INTO SELECT statement.
SQL Server INSERT multiple rows – examples
We will use the ‘user’ table from the ‘alumni’ database and the field
will be ‘iduser’.
Multi-Row Insert Example
The following statement inserts multiple rows into the ‘user’ table:
INSERT INTO `alumni`.`user` (`iduser`) VALUES (5), (6), (10), (8);
Don’t forget to close the last record with a semicolon.
To check the result, you can use the SELECT statement:
SELECT userid FROM `alumni`.`user`;
Obtaining as a result the previously inserted values:
INSERT INTO with multiple fields
If it is multiple columns (fields), then the INSERT statement syntax is
INSERT INTO table_name (column1, column2, column3) VALUES (value1,
Example of simple insertion of values in a table:
INSERT INTO `alumni`.`iduser`
(`idnumber`, `name`, `color`, `valstat`)
(7, ‘Randy’, ‘Green’, 150),
(2, ‘Paul’, ‘Gray’, 120),
(12, ‘Mark’, ‘Yellow’, 210),
(12, ‘Mark’, ‘Brown’, 310),
(2, ‘Paul’, ‘Gray’, 310),
(2, ‘Paul’, ‘Brown’, 450),
(7, ‘Randy’, ‘Brown’, 150),
(7, ‘Randy’, ‘Yellow’, 240),
(2, ‘Paul’, ‘Yellow’, 80);
And the result, when you run the query:
It is the following:
Most Common Errors with the INSERT INTO Statement (Using Quotes)
The error known as: “The INSERT INTO statement contains the following
unknown field name: ‘|’ Please make sure you have spelled the name correctly
and try the operation again”, it is due to an error in spelling the field
name, or possibly you are typing the wrong character (`).
Another common mistake that occurs when writing an MySQL project is using
double quotes instead of single quotes. Single quotes are used to delimit
strings. For example, double quotes are used here instead of single
quotes, which causes an error.
Finding SQL syntax errors
Finding SQL syntax errors can be tricky, but there are some tips on how
to make it a little easier. Using the Error List function helps a lot.
It allows the user to review errors while still writing the project and
avoid searching through thousands of lines of code.