• Messages 1-500
• Messages 501-1000
• Messages 1001-1500
• Messages 1501-2000
• Messages 2001-3000
• Messages 3001-4000
• Messages 4001-5000
• Messages 5001-6000
• Messages 6001-7000
• Messages 7001-7500
• Messages 7501-8000
• Messages 8001-8500
• Messages 8501-9000
• Messages 9001-9500
• Messages 9501-10000
• Messages 10001-10500
• Messages 10501-11000
• Messages 11001-11500
• Messages 11501-12000
• Messages 12001-13000
• Messages 13001-13500
• Messages 14001-14500

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

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:

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

• Frequently Asked Questions - SQL Server Error Messages
• Tips & Tricks - SQL Server Error Messages 1 to 500