28

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.

d219
  • 2,275
  • 5
  • 21
  • 29
kacalapy
  • 8,667
  • 18
  • 69
  • 111

3 Answers3

53

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
Martin Smith
  • 402,107
  • 79
  • 682
  • 775
  • PK shouldn't be necessary for an identity column. – Sam Dec 07 '10 at 23:41
  • @Sam - He specifies he wants it as the PK. Why do you say it shouldn't be necessary anyway? He might well want the unique constraint enforcement, and clustered index that comes with defining it as PK. – Martin Smith Dec 07 '10 at 23:42
  • i dont want dups, so i thought primary key would be perfect. i want to use it later in a while loop – kacalapy Dec 07 '10 at 23:43
  • 1
    @kacalapy - Try running `DROP TABLE #tmp` I suspect that you may have an old version messing things up. – Martin Smith Dec 07 '10 at 23:45
  • thanks martin, i did have an old #tmp, droped it and its fine now... should i use #tmp or @tmp ? – kacalapy Dec 07 '10 at 23:48
  • Probably `@tmp` - That is slightly lower overhead in terms of recompilations and logging. The trade off is that statistics aren't maintained for them so sometimes you can end up with sub optimal plans when using them in a join. However I think it will recognise even without statistics that a select on `id` will return a maximum of 1 row because of the unique constraint. – Martin Smith Dec 07 '10 at 23:51
  • @Martin - The PK is not necessary for the identity insert to work and that is the problem he encountering. – Sam Dec 07 '10 at 23:54
  • 1
    @Sam - Don't know about you but I'm answering the following question `how can i make a sql temp table with primary key and autoincrmenting field?` You appear to be answering half of it! – Martin Smith Dec 07 '10 at 23:55
10

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

Udo Held
  • 11,486
  • 11
  • 63
  • 86
Devin
  • 101
  • 1
  • 2
6

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_knievel
  • 700
  • 6
  • 14