INTEGER IDENTITY -WITH behaves differently from CREATE TABLE

Question

Post reply

INTEGER IDENTITY -WITH behaves differently from CREATE TABLE

brosspaxedi

SSCommitted

Points: 1591
More actions
May 25, 2010 at 9:27 am

#225061

This code works:
SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME
CREATE TABLE #tTables
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)
INSERT INTO #tTables (strTableName)
SELECT name FROM dbo.sysobjects WHERE xtype = 'u'
SET @lngTabCount = @@ROWCOUNT
SET @lngLoopCount = @lngTabCount
WHILE @lngLoopCount <> 0
BEGIN
SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID =
@lngLoopCount)
EXEC sp_spaceused @strTabName
SET @lngLoopCount = @lngLoopCount - 1
END
DROP TABLE #tTables
GO
When I try to change it to a "With" construction, it chokes on INTEGER IDENTITY. Why is that?
*******************
What I lack in youth, I make up for in immaturity!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

brosspaxedi SSCommitted Points: 1591 More actions · Answer 1

Thanks for the responses.

But this doesn't resolve the issue. I am providing the script so you can compare the results in SSMS...

See the Create statement and the With statement below:

SET NOCOUNT ON

DECLARE @lngTabCount INTEGER

DECLARE @lngLoopCount INTEGER

DECLARE @strTabName SYSNAME

CREATE TABLE #tTables -- this works

WITH #tTables -- this doesn't work

(

numID INTEGER IDENTITY(1,1)

,strTableName SYSNAME

)

INSERT INTO #tTables (strTableName)

SELECT name FROM dbo.sysobjects WHERE xtype = 'u'

SET @lngTabCount = @@ROWCOUNT

SET @lngLoopCount = @lngTabCount

WHILE @lngLoopCount <> 0

BEGIN

SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID =

@lngLoopCount)

EXEC sp_spaceused @strTabName

SET @lngLoopCount = @lngLoopCount - 1

END

DROP TABLE #tTables

GO

Comment/uncomment in SSMS and you'll see the difference.

*******************
What I lack in youth, I make up for in immaturity!

brosspaxedi SSCommitted Points: 1591 More actions · Answer 2

In other words, comment out the WITH line and it works.

Comment out the CREATE line and it doesn't.

*******************
What I lack in youth, I make up for in immaturity!

Paul White SSC Guru Points: 150468 More actions · Answer 3

It seems to me that the confusion has arisen through a misreading of the INSERT syntax, which does allow for a WITH common_table_expression (see http://msdn.microsoft.com/en-us/library/ms174335.aspx).

The second point is that a Common Table Expression (CTE) is not a definition of a real table in the CREATE TABLE sense. A CTE is a named result set, and pretty much the same idea as using a derived table in a SELECT query (except for the recursive capabilities of a CTE).

Books Online explains CTEs well, and provides some very clear examples:

http://technet.microsoft.com/en-us/library/ms175972.aspx

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Also - remember that CTE's only exist for one statement (it represents a temporary contruct being used and reused in a single statement). Even if that syntax were valid, the scope of the CTE would have destroyed the table long before you ever got into the WHILE loop.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?