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.

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

Read Full Post »

Sql Server : Keyboard shortcut to change to UPPER CASE in Sql Server Management Studio (SSMS)

Quick one today:

As alwways, there is value in formatting SQL code in a proper way. There are several ‘proper’ ways to format code; And all are great. To each, their own.

But one formatting tip that everyone appreciates is keeping Sql Server identifiers or keywords in UPPER CASE.

There is a keyboard shortcut, that allows this operation : Ctrl + Shift + U

--
--  Some random un-formatted code.
--
begin
set nocount on
declare @starttime datetime
declare @hierarchyid int
declare @nodeid bigint
declare @statuscode int

Highlight the word that you want to change to UPPER CASE and use the keyboard short cut: Ctrl + Shift + U. Similarly, changing to lower case could be accomplished with Ctrl + Shift + L

--
--  After formatting
--
BEGIN
SET NOCOUNT ON

DECLARE @starttime		DATETIME
DECLARE @hierarchyid	INT
DECLARE @nodeid			BIGINT
DECLARE @statuscode		INT

 

Hope this helps,
_Sqltimes

Read Full Post »