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.

Related concepts
IDENTITY Columns Usage
Update IDENTITY Columns