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

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

---

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?

---

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

---

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

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

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

---

--===== 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
;