Multiple Rows and Expressions

The other major form of the INSERT statement replaces the VALUES clause with a SELECT statement. This feature allows you to insert the following data:

• Multiple rows with only one statement (each time the SELECT statement returns a row, a row is inserted)
• Calculated values (the VALUES clause permits only constants) because the projection list can contain expressions

For example, suppose a follow-up call is required for every order that has been paid for but not shipped. The INSERT statement in the following example finds those orders and inserts a row in cust_calls for each order:

INSERT INTO cust_calls (customer_num, call_descr)
   SELECT customer_num, order_num FROM orders
      WHERE paid_date IS NOT NULL
      AND ship_date IS NULL

This SELECT statement returns two columns. The data from these columns (in each selected row) is inserted into the named columns of the cust_calls table. Then an order number (from order_num, a SERIAL column) is inserted into the call description, which is a character column. Remember that the database server allows you to insert integer values into a character column. It automatically converts the serial number to a character string of decimal digits.