IDENTITY (Function) (Transact-SQL)
Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.
![]() |
---|
To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers. |
Because this function creates a column in a table, a name for the column must be specified in the select list in one of the following ways:
--(1) SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable; --(2) SELECT ID_Num = IDENTITY(int, 1, 1) INTO NewTable FROM OldTable;
--(1) SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable; --(2) SELECT ID_Num = IDENTITY(int, 1, 1) INTO NewTable FROM OldTable;
The following example inserts all rows from the Contact table from the AdventureWorks database into a new table called NewContact. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the NewContact table.
USE AdventureWorks2012; GO IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL DROP TABLE Person.NewContact; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED; GO SELECT IDENTITY(smallint, 100, 1) AS ContactNum, FirstName AS First, LastName AS Last INTO Person.NewContact FROM Person.Person; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO SELECT ContactNum, First, Last FROM Person.NewContact; GO
USE AdventureWorks2012; GO IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL DROP TABLE Person.NewContact; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED; GO SELECT IDENTITY(smallint, 100, 1) AS ContactNum, FirstName AS First, LastName AS Last INTO Person.NewContact FROM Person.Person; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO SELECT ContactNum, First, Last FROM Person.NewContact; GO