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

You cannot use the union operator with an identity function in a select into.