Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Can't add identity column to a temp table???

Author  Topic 

mzanaty
Starting Member

8 Posts

Posted - 2002-01-30 : 22:53:29
I'm writing a paging stored proc, i.e. return n rows of a query starting at row m. It looked simple, just dump the query into a temp table, add an identity column for the row number, then select the desired row numbers. But SQL Server chokes on the last step. Here's an example.

-- dump to a temp table
select * into #temp from x
-- add an identity column for row numbers
alter table #temp add rownum int identity
-- get rows 1000 to 1100, i.e. paging the result set
select * from #temp where rownum >= 1000 and rownum <= 1100

The last line gives the error:
Invalid column name 'rownum'.

It's as if the alter didn't work on the temp table. But I know it did, because if I remove the where clause, the result set includes the rownum column. Any caveats with adding columns to temp tables? Or trying to use those columns later in a select? What's the best paging approach? (mySQL LIMIT and Oracle ROWNUM are sorely missed in MS-SQL!)

Thanks,
Mo Zanaty, mzanaty@cisco.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-30 : 23:07:20
Hi

Define the temp table first, with a create table statement, include your identity column, then all the columns you want to use. Then use :

INSERT INTO #temp (every, col, you, have)
Select * from YourSourceTable

Damian
Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-01-31 : 01:10:41
I Just Tried this too. This Works strange, if i execute this

select * into #temp from x
alter table #temp add rownum int identity
select * from #temp where rownum >= 1000 and rownum <= 1100

in a batch it gives me a error indicating invalid column name Rownum(though on removing the where clause on the last select it shows the rownum column )

but if i execute one-one instruction seperately it works fine.

Cant figure out the Reason . Any Insight on this??.


--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 01/31/2002 10:47:45
Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2002-01-31 : 11:05:39
SQL has problems resolving names of the columns that are added via ALTER TABLE in the same batch. I believe that sending ALTER TABLE commands in a separate batch (via a dynamic SQL call) should help to get around this. Try something along the lines of:

select * into #temp from x
exec ('alter table #temp add rownum int identity')
select * from #temp where rownum >= 1000 and rownum <= 1100



Go to Top of Page

mzanaty
Starting Member

8 Posts

Posted - 2002-01-31 : 19:52:46
This is a generic proc that knows nothing about the table structure. So I must use SELECT INTO not CREATE TABLE. It would be nice if I could add the row id column directly in the SELECT INTO, but SQL Server doesn't allow this. Any other ideas?

quote:

Define the temp table first, with a create table statement, include your identity column, then all the columns you want to use. Then use :
INSERT INTO #temp (every, col, you, have)
Select * from YourSourceTable



Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2002-01-31 : 21:32:13
quote:

It would be nice if I could add the row id column directly in the SELECT INTO, but SQL Server doesn't allow this. Any other ideas?



First of all, SQL Server does allow this (just use identity function):

select identity (int, 1,1) as idfld , * into #temp from sometable

Secondly, did you read through all the prior messages in this thread?

Edited by - izaltsman on 01/31/2002 21:42:25
Go to Top of Page

mzanaty
Starting Member

8 Posts

Posted - 2002-02-01 : 14:27:01
Yes, this is exactly the problem I'm hitting. However, the exec fix didn't work because these commands are just strings already passed to an exec. The generic proc builds all this info dynamically. It chokes on the nested exec. I guess you can only nest stored proc execs, not any SQL string. I can probably work around this by using a fixed temp table name and writing a 1-line alter proc that should exec even if nested. Many thanks for identifying the problem and providing a fix!
quote:

SQL has problems resolving names of the columns that are added via ALTER TABLE in the same batch. I believe that sending ALTER TABLE commands in a separate batch (via a dynamic SQL call) should help to get around this. Try something along the lines of:
select * into #temp from x
exec ('alter table #temp add rownum int identity')
select * from #temp where rownum >= 1000 and rownum <= 1100



Go to Top of Page

mzanaty
Starting Member

8 Posts

Posted - 2002-02-01 : 14:57:29
When I first tried 'select identity(int) as rownum,* into...' it failed so I assumed no dice with that approach. Now that you point out it should actually work, I revisited it to see exactly why it failed. And now I realize I will hit the same problem with alter. The underlying table often already has a real identity column. The temp table inherits it. So adding rownum as an identity fails, using select into or alter. Is there any way to remove the identity property from the real ID column in the temp table, without dropping it?

Go to Top of Page

izaltsman
A custom title

1139 Posts

Posted - 2002-02-01 : 15:45:10
No. Can't do that... However, who says that the field you add needs to be an identity? All you really want is for it to be sequential, right? Then how 'bout this:


declare @counter int -- We'll need this variable to generate sequence

set @counter = 0

select cast(NULL AS int) as new_id, * into #temp from northwind..categories

-- This generates the actual sequence
update #temp
set @counter = new_id = @counter + 1


select * from #temp
drop table #temp



Go to Top of Page

mzanaty
Starting Member

8 Posts

Posted - 2002-02-01 : 19:56:24
Many thanks for all the help! Here's the working code. Is this the best way to extract a page of an arbitrary query? Any optimizations or better approaches? (Note: For some reason, this forum editor hides plus.)

CREATE PROCEDURE dbo.sp_GetPage
(
@StartRow int, -- 0 means return all rows
@NumRows int, -- 0 means return all rows
@Qry varchar(5000) -- select query string
)
AS

IF @StartRow = 0 OR @NumRows = 0
EXEC (@Qry)
ELSE
BEGIN

-- Dump the select query to a temp table by inserting an INTO clause.
-- Only dump the rows up to the desired last row via ROWCOUNT.
-- Also add a RowNum column.
SET NOCOUNT ON
SET @Qry =
'SET ROWCOUNT ' + STR(@StartRow + @NumRows - 1) + ' ' +
STUFF(@Qry, CHARINDEX(' FROM ', UPPER(@Qry)), 0, ', RowNum = 0 INTO #Temp ') + ' ' +
'DECLARE @RowNum int ' +
'SET @RowNum = 0 ' +
'UPDATE #Temp SET @RowNum = RowNum = @RowNum+1 ' +
'SET ROWCOUNT ' + STR(@NumRows) + ' ' +
'SELECT * FROM #Temp WHERE RowNum >= ' + STR(@StartRow)
EXEC (@Qry)
SET ROWCOUNT 0
SET NOCOUNT OFF

END
GO


Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-12-29 : 22:58:19
quote:
Originally posted by izaltsman

No. Can't do that... However, who says that the field you add needs to be an identity? All you really want is for it to be sequential, right? Then how 'bout this:


declare @counter int -- We'll need this variable to generate sequence

set @counter = 0

select cast(NULL AS int) as new_id, * into #temp from northwind..categories

-- This generates the actual sequence
update #temp
set @counter = new_id = @counter + 1


select * from #temp
drop table #temp




I realize that this post is 10 years old but just wanted to set the record straight here. Yes, you CAN do that. If you lookup INTO in Books Online, you'll see why the following does all for the creation of a new IDENTITY column using SELECT/INTO where one of the columns is already an IDENTITY column. The old IDENTITY column loses the IDENTITY property and the new column becomes and IDENTITY column.

--===== Do this test in a nice, safe place that everyone has.
USE tempdb
;
--===== Create a table with an IDENTITY column in it.
CREATE TABLE dbo.Test
(
TestID INT IDENTITY(1,1),
SomeData VARCHAR(100)
)
;
--===== Add some test data to the test table
INSERT INTO dbo.Test
(SomeData)
SELECT 'This is a test.' UNION ALL
SELECT 'And, so is this.'
;
--===== Demonstrate a success because of the IDENTITY column
SELECT RowNum = IDENTITY(INT,1,1),
TestID = ISNULL(TestID,0),
SomeData
INTO #SuccessTest
FROM dbo.Test
;
--===== Show that the new column is an IDENTITY column
-- and that we've also made it NOT NULL
SELECT [name], is_identity, is_nullable
FROM sys.Columns
WHERE object_id = OBJECT_ID('#SuccessTest')
;
--===== Cleanup from the test
DROP TABLE dbo.Test, #SuccessTest
;



--Jeff Moden
Go to Top of Page
   

- Advertisement -