ASA SQL User's Guide
  Adding, Changing, and Deleting Data
    Adding data using INSERT

Adding new rows with SELECT

To pull values into a table from one or more other tables, you can use a SELECT clause in the INSERT statement. The select clause can insert values into some or all of the columns in a row.

Inserting values for only some columns can come in handy when you want to take some values from an existing table. Then, you can use update to add the values for the other columns.

Before inserting values for some, but not all, columns in a table, make sure that either a default exists, or you specify NULL for the columns for which you are not inserting values. Otherwise, an error appears.

When you insert rows from one table into another, the two tables must have compatible structures—that is, the matching columns must be either the same data types or data types between which Adaptive Server automatically converts.

Example 

If the columns are in the same order in their create table statements, you do not need to specify column names in either table. Suppose you have a table named newproduct that contains some rows of product information in the same format as in the product table. To add to product all the rows in newproduct:

INSERT product
SELECT *
FROM newproduct

You can use expressions in a SELECT statement inside an INSERT statement.

Inserting data into some columns 

You can use the SELECT statement to add data to some, but not all, columns in a row just as you do with the VALUES clause. Simply specify the columns to which you want to add data in the INSERT clause.

Inserting Data from the Same Table 

You can insert data into a table based on other data in the same table. Essentially, this means copying all or part of a row.

For example, you can insert new products, based on existing products, into the product table. The following statement adds new Extra Large Tee Shirts (of Tank Top, V-neck, and Crew Neck varieties) into the product table. The identification number is ten greater than the existing sized shirt:

INSERT INTO product
SELECT id+ 10, name, description,
    'Extra large', color, 50, unit_price
FROM product
WHERE name = 'Tee Shirt'