Execute the following T-SQL example script in Microsoft SQL Server Management Studio Query Editor to demonstrate the use of identity function in select into temporary table.

-- SQL Server identity function  - identity( data type, starting seed, increment)

-- Select into temporary table - SQL autonumber – sequence number

USE pubs;

SELECT   EmployeeNumber = emp_id,

         FirstName = fname,

         MiddleInitial = minit,

         LastName = lname,

         JobNumber = IDENTITY(SMALLINT,100,1),

         JobLevel = job_lvl,

         PublicationID = pub_id,

         HireDate = convert(CHAR(10),hire_date,112)

INTO     #Employee

FROM     employee

ORDER BY LastName,

         FirstName

GO

SELECT *

FROM   #Employee

GO

/* Partial results

EmployeeNumber    FirstName   MiddleInitial     LastName    JobNumber

PMA42628M         Paolo       M                 Accorti     100

PSA89086M         Pedro       S                 Afonso      101

VPA30890F         Victoria    P                 Ashworth    102

H-B39728F         Helen                         Bennett     103

L-B31947F         Lesley                        Brown       104

F-C16315M         Francisco                     Chang       105

PTC11962M         Philip      T                 Cramer      106

A-C71970F         Aria                          Cruz        107

*/

------------

Related articles:

IDENTITY (Function) (Transact-SQL)

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause