Insert Multiple Rows in SQL

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 statement:

INSERT INTO table_name (column_list)

VALUES (value_list_1),
(value_list_2), …
(value_list_n);

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.

Check Result

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
as follows:

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

Example of simple insertion of values in a table:

INSERT INTO `alumni`.`iduser`
(`idnumber`, `name`, `color`, `valstat`)
VALUES
(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:

SELECT *
FROM `alumni`.`user`;

It is the following:

iduser

idnumber	name	color	valstat
7	Randy	Brown	150
2	Paul	Brown	120
12	Mark	Yellow	210
12	Mark	Yellow	310
2	Paul	Yellow	310
2	Paul	Brown	450
7	Randy	Brown	150
7	Randy	Yellow	240
2	Paul	Yellow	80

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.

Uncategorized