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>),
                             ... ;

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

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

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

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

Clean up your table.


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


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');
-- Check your result
SELECT * FROM person;

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.



Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.

Manage research, learning and skills at Create an account using LinkedIn to manage and organize your omni-channel knowledge. is like a shopping cart for information -- helping you to save, discuss and share.

  Contact Us