Structured Query Language/INSERT 1

From Wikibooks, open books for an open world
Jump to: navigation, search
COMMIT and ROLLBACK Structured Query Language
INSERT 1
UPDATE 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[edit]

-- The static version of the INSERT command
INSERT INTO <tablename> (<list_of_columnnames>)
VALUES                  (<list_of_values>),
                        (<list_of_values>),
                        (<list_of_values>),
                             ... ;


Behind the tablename we can list the affected columns and after the keyword 'VALUES' one or more lists of values. Each list of values represents one new row. The lists of columns and values has to be such in accordance, 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 - 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 colums, but columnames 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 unneccessary 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[edit]

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 technic at page Advanced Insert. The other rules concerning number and sequence of columns or omitting values retains their validity.

Exercises[edit]

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;


COMMIT and ROLLBACK Structured Query Language
INSERT 1
UPDATE 1