The structure of INSERT command is
INSERT INTO <theTableNameToInsert> (<column1>, <column2>) VALUES (<value1ForColumn1>, <value1ForColumn2>), (<value2ForColumn1>, <value2ForColumn2>);
Basically we give it the table name, the order of the columns for every row (inside the first parenthesis, and then the values of the rows, every row in its own parenthesis.
The number of columns depend in the number of columns you want to insert and the number of columns in the table.The number of values will be the number of rows you want to insert. The order of the columns, must be the same to the order of the columns inside the values.
For our example we have a table called employee (in previous post we have shown how to create table, and the creation of employee). In that table we have the following columns and type: first_name TEXT, last_name TEXT, employee_number INT, salary INT.
We will insert the following row:
- Julia, Roberts, employee number 1, salary 50000
- Edison, Ford, employee number 2, salary 45000
- Irina, Shayk, employee number 3, salary 60000
- Yigal Arnon, employee number 4, salary 29000
So let see the code for this:
MariaDB [firstDB]> INSERT INTO employee (first_name, last_name, employee_number, salary) VALUES ("Julia", "Roberts", 1, 50000), ("Edison", "Ford", 2, 45000), ("Irina", "Shayk", 3, 60000), ("Yigal", "Arnon", 4, 29000); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0
As we can see, we have inserted 4 rows, and we know that because it say “4 rows affected”.
We also should be careful to write the values of the row in same order that we write the columns before the word “VALUES”.