Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Convert Oracle Math Functions to SQL Server Math Functions
Error Messages
Home > SQL Server Error Messages > Msg 177 - The IDENTITY function can only be used when the SELECT statement has an INTO clause.
SQL Server Error Messages - Msg 177 - The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Error Message

Server: Msg 177, Level 16, State 1, Line 1
The IDENTITY function can only be used when the
SELECT statement has an INTO clause.

Causes

As the message suggests, this error occurs when using the IDENTITY function in a SELECT statement without including the INTO clause of the SELECT statement. 

The IDENTITY function is used in a SELECT statement to insert an identity column into a new table.  Since it is used in a SELECT statement and it creates a column into a new table, the INTO clause is needed.  Although similar, the IDENTITY function is not the IDENTITY property that is used with the CREATE TABLE and ALTER TABLE DDL commands.

Some SQL Server developers try to use the IDENTITY function to return a sequence number for each row returned by a SELECT statement.  To illustrate, given the following table structure:

CREATE TABLE [dbo].[Employee] (
    [EmployeeNumber]           VARCHAR(10),
    [FirstName]                VARCHAR(100),
    [LastName]                 VARCHAR(100),
    [Salary]                   MONEY
)

The following statement, which tries to return a sequence number for each employee based on their salary, will generate the error:

SELECT IDENTITY(INT, 1, 1) AS [Rank], [EmployeeNumber], [FirstName], [LastName], [Salary]
FROM [dbo].[Employee]
ORDER BY [Salary] DESC

 
Msg 177, Level 15, State 1, Line 3
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Solution / Workaround:

To avoid this error, as the error suggests, include the INTO clause in the SELECT statement when using the IDENTITY function.  If the purpose of using the IDENTITY function in a SELECT statement is to return a sequence number to rank each record in the output, in SQL Server 2000 you have to create a temporary table then use that temporary table to return the result which will include the sequence number of each record.

In the example above, the following script can be used to return the list of employees ordered by their salary and ranking each employee from the highest paid employee to the lowest paid employee.

SELECT IDENTITY(INT, 1, 1) AS [Rank], [EmployeeNumber], [FirstName], [LastName], [Salary]
INTO #tmpEmployee
FROM [dbo].[Employee]
ORDER BY [Salary] DESC

 
SELECT [Rank], [EmployeeNumber], [FirstName],[LastName], [Salary]
FROM #tmpEmployee
ORDER BY [Rank]


DROP TABLE #tmpEmployee

If you are using SQL Server 2005 or SQL Server 2008 or higher, you can use the ROW_NUMBER function for this purpose, which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Here’s how the SELECT statement will look like using the ROW_NUMBER ranking function introduced in SQL Server 2005:

SELECT ROW_NUMBER() OVER (ORDER BY [Salary] DESC) AS [Row_Number], [EmployeeNumber], 
       [FirstName], [LastName], [Salary]
FROM [dbo].[Employee]

To illustrate the result of this SELECT statement, let’s assume that the [dbo].[Employee] table has the following rows of data:

EmployeeNumber FirstName LastName Salary
FB0001 Mark Zuckerberg 13500000000
MS001 Bill Gates 56000000000
APPLE001 Steve Jobs 8300000000
ORACLE001 Larry Ellison 39500000000
WALMART001 Samuel Robson Walton 21000000000
WALMART002 James Carr Walton 21000000000
WALMART003 Alice Walton 21000000000
WALMART004 Christy Walton 26500000000
GOOGLE001 Larry Page 19800000000

Executing the SELECT statement with the ROW_NUMBER ranking function above will generate the following result:

Row_Number EmployeeNumber FirstName LastName Salary
1 MS001 Bill Gates 56000000000
2 ORACLE001 Larry Ellison 39500000000
3 WALMART004 Christy Walton 26500000000
4 WALMART001 Samuel Robson Walton 21000000000
5 WALMART002 James Carr Walton 21000000000
6 WALMART003 Alice Walton 21000000000
7 GOOGLE001 Larry Page 19800000000
8 FB0001 Mark Zuckerberg 13500000000
9 APPLE001 Steve Jobs 8300000000
Related Articles :