How can I make a SQL temp table with primary key and auto-incrementing field?
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
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
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