Some time we have to do Row Numbering, for same we can use IDENTTY function with SELECT Statement, but we can’t use it with simply SELECT command, its always used with … INTO .. form as
SELECT Col1,Col2…..Coln INTO TableName from SourceTablename
If it tried with simply select command as
Select identity(int,1,1) IDD, EMPNAME,DEPT,BASICSALARY from Employee
Will give error as
Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
So if we want to do it, we have to pass Select statement as
Select IDENTITY(int,1,1) IDD, NAME,BASICSALARY into #TempEmp from Employee
SELECT * from #TempEmp
The IDENTITY gets three mandatory parameters, namely datatype of the identity column, starting value and the increment. With these, it can customize the new column according to our requirement. For example, an integer starting from 1 and incremented for each row with 1 can be specified as:
IDENTITY(INT,1,1)
Note : Suppose in source table there is already a Identity column exist, this column should be not in Select statement otherwise it will give a error, let in Employee Table, EMPID is an IDENTITY column, and we are passing Select statement as
Select IDENTITY(int,1,1) IDD, EMPID,NAME,BASICSALARY into #TempEmp from Employee
Select * from #TempEmp
Will throw error as
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘#TempEmp’, which already has column ‘EMPID’ that inherits the identity property.
WikiDBA 