rowcount in ''select into''

Is there a way to insert the rowcount in a 'select into' clause? Example:

select @.@.rowcount as id,* into mytable_with_rowcount from mytable

If mytable has 100 records then I want an id column in 'mytable_with_rowcount' and have the id column numbered 1 - 100. I know this can be done in a loop, but can it be done in the select w/out doing a loop?

Thanks,

Phil

if its sql server 2005 you can use Ranking Function to number the row and then insert to table. Read about Ranking Functions in BOL.

If its 2000, then you can create a table with ID as indentity column and then insert the rows to that table.

http://www.sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

http://www.sql-server-performance.com/ak_ranking_functions.asp

Madhu

|||Very nice. Thanks.