Select 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.

The syntax is:

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.