insert an auto increment column in a new query

[henky]

How can I add an auto increment row in a query?

Existing table: Names
Name
John
Jamie
Adolf
Nelson
Tony
George
Kevin

I want to make a select ... into query (to create a new table)

SELECT Name
INTO NewNames
FROM Names
ORDER BY Name
GO

The result has to be
ID Name
1| Adolf
2| George
3| Jamie
4| John
5| Kevin
6| Nelson
7| Tony

How can I insert the auto incement ID column in my posted code? I am a newby to T-SQL language.

 

[vongrunt]

You can try this:

select identity(int, 1,1) as ID, Name
into NewNames
from Names
order by Name

select * from NewNames

Btw. SQL knows no concept of prev/next row or record number, so better get used to it.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba