IDENTITY (Function) (T-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.

Syntax

IDENTITY(data_type[, seed, increment]) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these 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

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'employees')

    DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

    fname AS first,

    minit AS middle,

    lname AS last,

    IDENTITY(smallint, 100, 1) AS job_num,

    job_lvl AS job_level,

    pub_id,

    hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

See Also