如何创建带有主键和自增字段的SQL临时表？

Question

我这里缺少什么？ 我想要让ID字段成为主键并自动递增，这样就不需要显式插入它了。

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

当我尝试这个操作时出现了错误：

插入错误：提供的列名或值数量与表定义不匹配。

- kacalapy

---

不要偷懒，明确指定你想要插入的列。稍后你会感谢这样做的。 - SQLMason

Answer 1

据我所见，您只是缺少“主键”这几个词来满足您的指定目标。

对于其他列，最好明确定义它们应该是NULL还是NOT NULL，这样您就不会依赖ANSI_NULL_DFLT_ON设置。

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

---

身份列不应该需要PK。 - Sam

@kacalapy - 尝试运行 DROP TABLE #tmp，我怀疑你也许有一个旧版本在捣乱。 - Martin Smith

谢谢马丁，我确实有一个旧的 #tmp，已经删除了，现在没问题了... 我应该使用 #tmp 还是 @tmp？ - kacalapy

可能是 @tmp - 这在重新编译和日志记录方面的开销稍微低一些。这样做的折衷是它们的统计信息不会被维护，因此在使用它们进行连接时有时可能会得到次优的计划。但是我认为即使没有统计信息，它也会意识到对 id 的选择将返回最多 1 行，因为有唯一约束。 - Martin Smith

@Sam - 不知道你怎么样，但我正在回答下面的问题“如何创建一个带有主键和自增字段的SQL临时表？”你似乎只回答了一半！ - Martin Smith

Answer 2

如果你只是在做一些简单的临时工作，可以跳过输入显式的CREATE TABLE语句，直接使用SELECT...INTO创建临时表，并在选择列表中包含一个Identity字段。

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

Answer 3

不要插入标识字段。您需要指定字段名称并使用Values子句。

insert into #tmp (AssignedTo, field2, field3) values (value, value, value)

如果您使用insert into... select field field field，它将插入第一个字段到标识字段并且会崩溃。

- Devin