SQL Server Error Messages - Msg 177¶

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