How can I make a SQL temp table with primary key and auto-incrementing field

Question

What am I missing here  I m trying to get the ID field to be the primary key and auto increment so that I don t need to insert it explicitly   CREATE TABLE  tmp   ID INT IDENTITY 1  1    AssignedTo NVARCHAR 100   AltBusinessSeverity NVARCHAR 100   DefectCount int     insert into  tmp  select  user   high  5 union all select  user   med  4   select   from  tmp   I get an error with this saying       Insert Error  Column name or number of   supplied values does not match table   definition

User · Answer

you dont insert into identity fields   You need to specify the field names and use the Values clause  insert into  tmp  AssignedTo  field2  field3  values  value  value  value    If you use do a insert into    select field field field it will insert the first field into that identity field and will bomb

User · Answer

You are just missing the words  primary key  as far as I can see to meet your specified objective    For your other columns it s best to explicitly define whether they should be NULL or NOT NULL though so you are not relying on the ANSI NULL DFLT ON setting   CREATE TABLE  tmp   ID INT IDENTITY 1  1  primary key   AssignedTo NVARCHAR 100   AltBusinessSeverity NVARCHAR 100   DefectCount int     insert into  tmp  select  user   high  5 union all select  user   med  4   select   from  tmp

User · Answer

If you re just doing some quick and dirty temporary work  you can also skip typing out an explicit CREATE TABLE statement and just make the temp table with a SELECT   INTO and include an Identity field in the select list   select IDENTITY int  1  1  as ROW ID         Name into  tmp from  select  Bob  as Name union all       select  Susan  as Name union all       select  Alice  as Name  some data  select   from  tmp

[sql-server] How can I make a SQL temp table with primary key and auto-incrementing field?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to temp-tables