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