Structured Query Language/INSERT 1

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 is 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 shown 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 choose 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 that 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 retain their validity.

Exercises

Insert a new row for Mr. Peter Hufington with its body weight of 67 kg. He is born in LA.

Click to see solution

-- 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;