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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Good Question  amp  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

User · Answer

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

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

Examples related to sql

Examples related to sql-server