SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Can't add identity column to a temp table???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mzanaty
Starting Member

8 Posts

Posted - 01/30/2002 :  22:53:29  Show Profile  Reply with Quote
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!

Australia
4970 Posts

Posted - 01/30/2002 :  23:07:20  Show Profile  Visit Merkin's Homepage  Reply with Quote
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

United Arab Emirates
1408 Posts

Posted - 01/31/2002 :  01:10:41  Show Profile  Reply with Quote
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

USA
1139 Posts

Posted - 01/31/2002 :  11:05:39  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with 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 - 01/31/2002 :  19:52:46  Show Profile  Reply with Quote
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

USA
1139 Posts

Posted - 01/31/2002 :  21:32:13  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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 - 02/01/2002 :  14:27:01  Show Profile  Reply with Quote
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 - 02/01/2002 :  14:57:29  Show Profile  Reply with Quote
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

USA
1139 Posts

Posted - 02/01/2002 :  15:45:10  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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 - 02/01/2002 :  19:56:24  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 12/29/2012 :  22:58:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000