Progress
SQL-89
Guide and Reference


Inserting Rows

The INSERT INTO statement inserts one or more rows into an existing table. The data you insert can be a list of values that you supply or values from another table.

The INSERT INTO statement has the following syntax.

SYNTAX
INSERT INTO table-name 
  [ ( column-list ) ]
  { VALUES ( value-list ) | SELECT-statement } 

The VALUES clause contains a list of values that you specify in your SQL statement. If you use the VALUES clause, you can insert only one row at a time into the table. The value-list can be one value or a series of values separated by commas. Each value can be a literal, the keyword NULL, a variable, a field defined and assigned a value elsewhere, or an expression. Each character string literal must be enclosed in either single or double quotation marks.

You can also insert values from another table by using a SELECT statement within the INSERT INTO statement.

This example inserts a new customer by specifying a list of values.

INSERT INTO Customer (Name, Address, City, State, Postal-Code, Cust-Num)
     VALUES (’Cycle Pro’, ’30 Boynton Street’,
             ’Jamaica Plain’, ’MA’, ’02130’, 101). 

SQL inserts the first value into the first column in the list, the second value into the second column. Therefore, the number of columns must be the same as the number of values. If you specify a column list that does not include all columns of the table, SQL assigns the null or default value to each column omitted from the list. If you omit the column list entirely, SQL inserts the values into the columns in the order in which you created the columns. In this case, there must be a value for every column in the table.

If you use the SELECT statement with the INSERT INTO statement, all the rows that satisfy the SELECT statement are inserted into the table. The number and order of columns in the SELECT statement must match the implicit or explicit column list in the INSERT INTO statement. If you specify SELECT *, the asterisk is expanded into a list of all columns in the FROM clause table(s).

The following example inserts rows into a preferred customer table by selecting data from the customer table. It inserts a row for each customer whose balance exceeds $50,000.

INSERT INTO Prefer_Cust
    (Name, Address, City, State, Postal-Code, Cust-Num)
    SELECT Name, Address, City, State, Postal-Code, Cust-Num
    FROM Customer
    WHERE Balance > 50000. 

When you use the INSERT INTO statement to add rows using a view, you can insert values only into columns that are defined in the view definition. (See "Data Definition Language," for more information.) Any other columns in the underlying table for the view are set to their default value, or to null if there is no default.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095