Can select into add a new field to the resulting table?

Question

I've got a SQL statement in SQL Server 2005 that looks something like this:

SELECT * INTO #TempTable FROM FirstTable WHERE <complex where clause>

What I would really, really like is to have the resulting temp table have an extra field that is essentially an integer field counting from 1 up in the order the where clause returned the records.

Is this possible?

(Essentially, I want to be able to return a subset of the records returned from the first select statement, along the lines of "lines 45 through 179".)

Answer 1

Try this, using Row_Number:

-- insert into temp table
SELECT *, 
  ROW_NUMBER() OVER (ORDER BY SortColumn) AS SortColumn INTO #TempTable 
FROM FirstTable 
WHERE <complex where clause>

-- check the results and drop the table
SELECT * FROM #TempTable WHERE SortColumn BETWEEN 45 AND 179 ORDER BY SortColumn
DROP TABLE  #TempTable

Obviously you'll need to replace SortColumn with whatever makes sense in your case

---

Edit:

If you're just trying to do paging, there are lots of examples of that:

• http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
• http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005
• http://www.google.com/search?q=sql+server+2005+paging