Home

	Expand Search

Transact-SQL User's Guide

Chapter 8 Adding, Changing, and Deleting Data

Adding new data

Adding new rows with select

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 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 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 do not need to 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.

You can use computed columns in a select statement inside an insert statement. For example, imagine that a table named tmp contains some new rows for the titles table, which contains some out-of-date data--the price figures need to be doubled. A statement to increase the prices and insert the tmp rows into titles looks like:

insert titles 
select title_id, title, type, pub_id, price*2,
    advance, total_sales, notes, pubdate, contract
from tmp

When you perform computations on a column, you cannot use the select * syntax. Each column must be named individually in the select list.

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.

For example, some authors in the authors table do not have titles and, therefore, do not have entries in the titleauthor table. To pull their au_id numbers out of the authors table and insert them into the titleauthor table as placeholders, try this statement:

insert titleauthor (au_id)
select au_id
    from authors
    where au_id not in
    (select au_id from titleauthor)

This statement is not legal, because a value is required for the title_id column. Null values are not permitted and no default is specified. You can enter the dummy value "xx1111" for titles_id by using a constant, as follows:

insert titleauthor (au_id, title_id)
select au_id, "xx1111"
    from authors
    where au_id not in
    (select au_id from titleauthor)

The titleauthor table now contains four new rows with entries for the au_id column, dummy entries for the title_id column, and null values for the other two columns.

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 a new row in the publishers table that is based on the values in an existing row in the same table. Make sure you follow the rule on the pub_id column:

insert publishers 
select "9999", "test", city, state 
    from publishers 
    where pub_name = "New Age Books"

 
(1 row affected) 

select * from publishers

 
pub_id  pub_name              city        state 
------- --------------------- -------     ------ 
0736    New Age Books         Boston      MA 
0877    Binnet & Hardley      Washington  DC 
1389    Algodata Infosystems  Berkeley    CA 
9999    test                  Boston      MA 
 
(4 rows affected) 

The example inserts the two constants ("9999" and "test") and the values from the city and state columns in the row that satisfied the query.