INSERT of many rows at once

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:

1INSERT INTO autorzy (imie, nazwisko, kraj, rok_urodzenia)
2VALUES
3  ('Eratostenes', 'z Cyreny', 'Grecja', -276),
4  ('Hypatia', 'z Aleksandrii', 'Egipt', 360),
5  ('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

1INSERT INTO kategorie (nazwa)
2VALUES ('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!