Hi all,

Does anyone know if theres a way to add an identity column into a
SELECT...INTO Query?  Either that or can you add one into an existing
table.

Thanks Alot!

Rob.

How about the IDENTITY() function?

Show quoteHide quote

HI Robken

Try this :

Select lastname, firstname  into #Foo
from Employees

Alter table #foo add counter int Identity(1,1)
Select * from #foo
drop table #foo

/*
lastname             firstname  counter
-------------------- ---------- -----------
Davolio              Nancy      1
Fuller               Andrew     2
Leverling            Janet      3
Peacock              Margaret   4
Buchanan             Steven     5
Suyama               Michael    6
King                 Robert     7
Callahan             Laura      8
Dodsworth            Anne       9

(9 row(s) affected)
*/

All the best

Baj-SGC818

robken wrote:
Show quoteHide quote

Use identity() function:

select identity(int,1,1) as id, name
into #tmp
from sysdatabases

HTH,

Dean

Show quoteHide quote

Just a picky note; you might want to use a @table variable instead of a temp
#table, less overhead.
Show quoteHide quote

Too bad you can't SELECT ...  INTO a table variable..

Dean

random select a row based a weight column.
Timeout expire
Optimize this query
store results over network
Variable table name in a cursor declaration