Hint: Be carefull and deactivate AUTOCOMMIT.
The INSERT command stores one or more new rows into one table. The content of the new rows consists of either fixed values or results from a SELECT, which is evaluated at runtime. So there are two different syntaxes to do the job.
Static Insert
-- The static version of the INSERT command
INSERT INTO <tablename> (<list_of_columnnames>)
VALUES (<list_of_values>),
(<list_of_values>),
(<list_of_values>),
... ;
Following the tablename we can list the affected columns and after the keyword 'VALUES' state one or more lists of values to insert. Each list of values represents one new row. The lists of columns and values have to be in accordance such that the quantity of list entries are identical and their data types correlate.
-- One value list results in one new row.
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
VALUES (91, 'Larry, no. 91', 'Goldstein', DATE'1970-11-20', 'Dallas', '078-05-1120', 95);
COMMIT;
-- The SQL standard - but not all implementations, in particular Oracle - supports a 'row value constructor' by
-- enumerate values inside a pair of parenthesis as show in the above green box.
-- Three lists of values (= row value constructors) result in three new rows. Please note the comma after all
-- but the last one.
INSERT INTO person (id, firstname, lastname, date_of_birth, place_of_birth, ssn, weight)
VALUES (92, 'Larry, no. 92', 'Goldstein', DATE'1970-11-20', 'Dallas', '078-05-1120', 95),
(93, 'Larry, no. 93', 'Goldstein', DATE'1970-11-20', 'Dallas', '078-05-1120', 95),
(94, 'Larry, no. 94', 'Goldstein', DATE'1970-11-20', 'Dallas', '078-05-1120', 95);
COMMIT;
We can chose any order of columns, but column names and values should be in concordance.
-- Sometimes things are scrambled. Maybe confusing, but works fine. See weight and id.
INSERT INTO person (date_of_birth, firstname, ssn, lastname, place_of_birth, weight, id)
VALUES (DATE'1970-11-20', 'Larry, no. 95', '078-05-1120', 'Goldstein', 'Dallas', 95, 95);
COMMIT;
We can omit unnecessary columns.
-- Depending on CREATE TABLE statement the missing columns will get the 'null special marker' or a default value.
INSERT INTO person (id, firstname, lastname, weight)
VALUES (96, 'Larry, no. 96', 'Goldstein', 95);
COMMIT;
Clean up your table.
DELETE FROM person WHERE id BETWEEN 91 AND 96;
COMMIT;
Dynamic Insert
Unlike in the above paragraph we can insert values which are not fixed but dynamic such that they are evaluated at runtime from any table, a function or a computation. Even the number of new rows can be dynamic. All this is done in a subselect which replaces the VALUE clause. We explain this technique at page Advanced Insert. The other rules concerning number and sequence of columns or omitting values retains their validity.
Exercises
Insert a new row for Mr. Peter Hufington with its body weight of 67 kg. He is born in LA.
-- Choose any free id
INSERT INTO person (id, firstname, lastname, weight, place_of_birth)
VALUES (81, 'Peter, no. 81', 'Hufington', 67, 'Los Angeles');
COMMIT;
-- Check your result
SELECT * FROM person;