Adding new rows with select

To pull values into a table from one or more other tables, 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 may be convenient when you want to take some values from an existing table. You can then 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 a default exists, or that NULL has been specified for the columns for which you are not inserting values. Otherwise, Adaptive Server returns an error message.

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 datatypes or datatypes between which Adaptive Server automatically converts.

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

insert authors 
select * 
from newauthors 

To insert rows into a table based on data in another table, the columns in the two tables need not be listed in the same sequence in their respective create table statements. You can use either the insert or the select statement to order the columns so that they match.

For example, suppose the create table statement for the authors table contained the columns au_id, au_fname, au_lname, and address, in that order, and newauthors contained au_id, address, au_lname, and au_fname. The column sequence must match in the insert statement. You could do this by using:

insert authors (au_id, address, au_lname, au_fname) 
select * from newauthors

or:

insert authors 
select au_id, au_fname, au_lname, address 
    from newauthors 

If the column sequence in the two tables fails to match, Adaptive Server either cannot complete the insert operation, or completes it incorrectly, putting data in the wrong column. For example, you might get address data in the au_lname column.