In MS-SQL, how do I 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

IIRC, the INSERT INTO command uses the schema of the source table to create the temp table. That's part of the reason you can't just try to create a table with an additional column. Identity columns are internally tied to a SQL Server construct called a generator.

Answer 2

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 3

You could do a Select Into, which would create the table structure on the fly based on the fields you select, but I don't think it will create an identity field for you.

Answer 4

To make things efficient, you need to do declare that one of the columns to be a primary key:

ALTER TABLE #mytable
ADD PRIMARY KEY(KeyColumn)

That won't take a variable for the column name.

Trust me, you are MUCH better off doing a : CREATE #myTable TABLE (or possibly a DECLARE TABLE @myTable) , which allows you to set IDENTITY and PRIMARY KEY directly.

Answer 5

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

Answer 6

Hey Now,

Good Question & Matt's was a good answer. To expand on the syntax a little if the oldtable has an identity a user could run the following:

SELECT col1, col2, IDENTITY( int ) AS idcol

  INTO #newtable

  FROM oldtable

--

That would be if the oldtable was scripted something as such:

CREATE TABLE [dbo].[oldtable]
(
    [oldtableID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [col1] [nvarchar](50) NULL,
    [col2] [numeric](18, 0) NULL,
)

Thx 4 the info this question helped me,

Catto

Answer 7

If you want to include the column that is the current identity, you can still do that but you have to explicitly list the columns and cast the current identity to an int (assuming it is one now), like so:

select cast (CurrentID as int) as CurrentID, SomeOtherField, identity(int) as TempID 
into #temp
from myserver.dbo.mytable