SQL and MySQL course Β· Module 5: Modifying data (DML)

INSERT of many rows at once

2 min read Β·
In this lesson4

When a whole crate of books arrives at the Library, the scribe does not write them in one by one. With a single INSERT command we can add many rows at once.

Syntax with multiple VALUES sets

It is enough, after the word VALUES, to list several parentheses separated by commas:

INSERT INTO autorzy (imie, nazwisko, kraj, rok_urodzenia)
VALUES
  ('Eratostenes', 'z Cyreny', 'Grecja', -276),
  ('Hypatia', 'z Aleksandrii', 'Egipt', 360),
  ('Klaudiusz', 'Ptolemeusz', 'Egipt', 100);

This command will write in three authors in one stroke. Each set of parentheses is one new row (scroll).

Why is this better?

  • Faster - the Library runs one command instead of three separate ones.
  • Clearer - it is obvious at once that we are adding a group of related records.
  • Safer - either all rows are added, or (on an error) none are.

Remember the consistency

In each set of parentheses there must be as many values as the columns you listed at the start - and always in the same order. If a value is missing in one set, the Library will refuse to run the whole command.

Example: new categories

INSERT INTO kategorie (nazwa)
VALUES ('Astronomia'), ('Geografia'), ('Filozofia');

Here we fill in only one column nazwa, so each set of parentheses contains a single value. Three new thematic sections in the blink of an eye!

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. How do you add several rows at once with a single INSERT command?

  2. 2. How many values must be in EACH set of parentheses in a multi-row INSERT?

Hands-on tasks in the game

  • Code editor

    Write a command that with a single INSERT adds to the kategorie table three new rows in the nazwa column: 'Astronomia', 'Geografia' and 'Filozofia'.

  • Vertical ordering

    Arrange the lines of the INSERT command in the correct order (top to bottom, as in the editor):

  • Click in order

    Click the elements in the order in which we build a command that adds one row:

  • Code editor

    Write a command that with a single INSERT adds to the autorzy table two authors (columns imie, nazwisko): 'Hypatia', 'z Aleksandrii' and 'Klaudiusz', 'Ptolemeusz'.

  1. QuestionAdding many rows
  2. Coding taskAdd many categories at once
  3. Put in orderLines of an INSERT for a category
  4. QuestionMatching the number of values
  5. Click in orderAdding the Astronomia category
  6. Coding taskAdd many authors at once

Useful articles