sqlsql-server

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


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?


Solution

  • Oh ye of little faith:

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

    IDENTITY (Function) (Transact-SQL)

    old docs link