SQL and MySQL course Β· Module 5: Modifying data (DML)
INSERT of many rows at once
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. How do you add several rows at once with a single INSERT command?
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'.