CODEWITHSUNDEEP
sql-servert-sqlselect
Electrons_Ahoy
Electrons_Ahoy

Reputation: 38613

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

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".)

Upvotes: 2

Views: 1734

Answers (1)

Michael Haren
Michael Haren

Reputation: 108296

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:

Upvotes: 5

Related Questions