Sql Server Error : The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Quick one today:

Recently, ran into an interesting error message that looks very complicated, but is fairly straight one.

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

The code looked something like this:

--
-- Error code
--
SELECT    ID = IDENTITY(INT, 1,1)
        , Name
        , type_desc
FROM sys.objects
GO

This is an interesting usage of IDENTITY() function – with INT datatype declaration within the function itself, with SEED value.

Looks like for such usage, we need to use INTO clause redirecting the return dataset into a table. Like this:

--
-- Correct code
--
SELECT    ID = IDENTITY(INT, 1,1)
        , Name
        , type_desc
INTO #Test_Table
FROM sys.objects
GO

Hope this helps,
_Sqltimes