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. 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 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.
You cannot insert data from a table that allows null
values into a table that does not, if any of the data being inserted
is null.
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 do not have to 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. You would have to make the column sequence match in the insert statement. You could do this in either of two ways:
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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
