Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Creating new tables from query results: select into

Using select into with IDENTITY columns

This section describes special rules for using the select into command with tables containing IDENTITY columns.

Selecting an IDENTITY column into a new table

To select an existing IDENTITY column into a new table, include the column name (or the syb_identity keyword) in the select statement's column_list:

select column_list 
     into table_name
     from table_name

The following example creates a new table, stores_cal_pay30, based on columns from the stores_cal table:

select record_id, stor_id, stor_name
into stores_cal_pay30
from stores_cal
where payterms = "Net 30"

The new column inherits the IDENTITY property, unless any of the following conditions is true:

The IDENTITY column is selected more than once.
The IDENTITY column is selected as part of an expression.
The select statement contains a group by clause, aggregate function, union operator, or join.

Selecting the IDENTITY column more than once

A table cannot have more than one IDENTITY column. If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.

In the following example, the record_id column, which is selected once by name and once by the syb_identity keyword, is defined as NOT NULL in stores_cal_pay60:

select syb_identity, record_id, stor_id, stor_name
into stores_cal_pay60
from stores_cal
where payterms = "Net 60"

Adding a new IDENTITY column with select into

To define a new IDENTITY column in a select into statement, add the column definition before the into clause. The definition includes the column's precision but not its scale:

select column_list
     identity_column_name = identity(precision)
     into table_name
     from table_name

The following example creates a new table, new_discounts, from the discounts table and adds a new IDENTITY column, id_col:

select *, id_col=identity(5)
into new_discounts
from discounts

If the column_list includes an existing IDENTITY column, and you add a description of a new IDENTITY column, the select into statement fails.

Defining a column whose value must be computed

IDENTITY column values are generated by Adaptive Server. New columns that are based on IDENTITY columns, but whose values must be computed rather than generated, cannot inherit the IDENTITY property.

If a table's select statement includes an IDENTITY column as part of an expression, the resulting column value must be computed. The new column is created as NULL if any column in the expression allows a NULL value. Otherwise, it is NOT NULL.

In the following example, the new_id column, which is computed by adding 1000 to the value of record_id, is created NOT NULL:

select new_id = record_id + 1000, stor_name
into new_stores
from stores_cal

Column values are also computed if the select statement contains a group by clause or aggregate function. If the IDENTITY column is the argument of the aggregate function, the resulting column is created NULL. Otherwise, it is NOT NULL.

IDENTITY columns selected into tables with unions or joins

The value of the IDENTITY column uniquely identifies each row in a table. However, if a table's select statement contains a union or join, individual rows can appear multiple times in the result set. An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL. Otherwise, it is NOT NULL.

For more information, see "Using IDENTITY columns" and "Updating IDENTITY columns". See also select in the Reference Manual.


	Expand Search