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

sql-server, sql-server-2005, temp-tables

Solution

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

Problem

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.

Original source