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.

Answer 1

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

Answer 2

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