INSERT INTO a temp table, and have an IDENTITY field created, without first declaring the temp table?

Question

I need to select a bunch of data into a temp table to then do some secondary calculations; To help make it work more efficiently, I would like to have an IDENTITY column on that table. I know I could declare the table first with an identity, then insert the rest of the data into it, but is there a way to do it in 1 step?

Answer 1

Oh ye of little faith:

SELECT *, IDENTITY( int ) AS idcol   INTO #newtable   FROM oldtable 

http://msdn.microsoft.com/en-us/library/aa933208(SQL.80).aspx

Answer 2

You commented: not working if oldtable has an identity column.

I think that's your answer. The #newtable gets an identity column from the oldtable automatically. Run the next statements:

create table oldtable (id int not null identity(1,1), v varchar(10) )  select * into #newtable from oldtable  use tempdb GO sp_help #newtable 

It shows you that #newtable does have the identity column.

If you don't want the identity column, try this at creation of #newtable:

select id + 1 - 1 as nid, v, IDENTITY( int ) as id into #newtable      from oldtable