INSERT Statement

This form of the INSERT statement that uses the VALUES clause without specifying column names can be used to insert lists of values into a table. You specify a value for each column in the table. One row is inserted for each VALUES clause. The order of the values in the VALUES clause should match the order of the columns in the table.

The following code fragment shows how the INSERT statement could be used with the VALUES clause to insert data values into the Customers table that was created in CREATE TABLE Statement. The Customers table has columns CustId, Name, Address, City, State, Country, PhoneNumber, and InitOrder.

insert into customers values (1,'Peter Frank', '300 Rock Lane', 'Boulder', 'CO', 
'United States', '3039564321', date '2012-01-14');
insert into customers values (2,'Jim Stewart', '1500 Lapis Lane', 'Little Rock', 
'AR', 'United States', '8705553978', date '2012-03-20');
insert into customers values (3,'Janet Chien', '75 Jujitsu', 'Nagasaki', '', 'Japan', 
'01181956879932', date '2012-06-07');
insert into customers values (4,'Qing Ziao', '10111 Karaje', 'Tokyo', '', 'Japan', 
'0118136774351', date '2012-10-12');
insert into customers values (5,'Humberto Sertu', '876 Avenida Blanca', 'Buenos Aires', 
'', 'Argentina','01154118435029', date '2012-12-15');

These statements add data values to the Sales table that was created in the CREATE TABLE Statement. The Sales table has columns ProdID, CustID, Totals, and Country.

insert into sales values (3234, 1, 189400, 'United States');
insert into sales values (1424, 3, 555789, 'Japan');
insert into sales values (3421, 4, 781183, 'Japan');
insert into sales values (3421, 2, 2789654, 'United States');
insert into sales values (3975, 5, 899453, 'Argentina');

Note that character values are enclosed in quotation marks.

This form of the INSERT statement that uses the VALUES clause with specific column names can also be used to insert lists of values into a table. You give values for the columns specified in the list of column names. The column names do not have to be in the same order as they appear in the table, but the order of the values in the VALUES clause must match the order of the column names in the INSERT column list. One row is inserted for each VALUES clause.

In the following code fragment, column names are used to add values to the Products table that was created in the CREATE TABLE Statement..

insert into products (prodid, product) values (1424, 'Rice');
insert into products (prodid, product) values (3421, 'Corn');
insert into products (prodid, product) values (3234, 'Wheat');
insert into products (prodid, product) values (3485, 'Oat');

The column list enables you to add a row that contains values for specified columns. If you do not specify the column list, values for every column in the table must be provided by the VALUES clause.

This form of the INSERT statement uses a query expression that inserts rows that were selected from another table or tables. The order of the values returned by the query expression should match the order of the columns that are specified in the list of column names. All the rows that are returned by the query expression are inserted into the table. The following restrictions should be noted when you use this form of the INSERT statement.

Here is an example of an INSERT statement that contains a query expression.

insert into salesum (
    sum_sales,
    max_sale,
    min_sale,
    avg_sale)
values (
    (select sum(totals) from sales),
    (select max(totals) from sales),
    (select min(totals) from sales),
    (select avg(totals) from sales)
); 

This example uses subqueries to populate a new table, SalesSum, with aggregate values from the Sales table. A subquery returns only one row. To see the content of the table, see Sales. For more information, see Overview of Subqueries.