CODEWITHSUNDEEP
sqlsql-server
Dale B
Dale B

Reputation: 513

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?

Upvotes: 51

Views: 162445

Answers (7)

Matt Rogish
Matt Rogish

Reputation: 24873

Oh ye of little faith:

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

IDENTITY (Function) (Transact-SQL)

old docs link

Upvotes: 97

Amy B
Amy B

Reputation: 110111

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.

Upvotes: 1

Catto
Catto

Reputation: 6409

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,
)

Upvotes: 4

RichM
RichM

Reputation: 1

If after the *, you alias the id column that is breaking the query a secondtime... and give it a new name... it magically starts working.

select IDENTITY( int ) as TempID, *, SectionID as Fix2IDs
into #TempSections
from Files_Sections

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

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

Upvotes: 2

boes
boes

Reputation: 2855

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

Upvotes: 6

Hector Sosa Jr
Hector Sosa Jr

Reputation: 4250

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.

Upvotes: -4

Related Questions