Sybase含IDENTITY字段的临时表INSERT INTO操作问题

首先，你的问题根源在于对IDENTITY列的使用理解有误：IDENTITY列是由数据库自动维护的自增字段，你不能在INSERT语句中主动给它赋值（除非特意开启IDENTITY_INSERT，但你的场景完全不需要这么做），而且@@identity在这里完全不适用——它返回的是当前会话中最近生成的标识值，在你执行INSERT之前这个值根本不存在，所以系统会提示非法IDENTITY值。

正确的基础写法 #

直接省略IDENTITY列（id）的插入，数据库会自动为每一行生成唯一的自增值：

CREATE TABLE #someTemp ( 
    id INT IDENTITY UNIQUE, 
    foo1 VARCHAR (20), 
    foo2 VARCHAR (80), 
    rand NUMERIC 
)

-- 插入时只指定非IDENTITY字段
INSERT INTO #someTemp (foo1, foo2, rand) 
SELECT v.foo_bar, v.bar_foo, v.list 
FROM #otherTemp a 
INNER JOIN some_table v ON v.id = a.id AND v.suu = a.ooo 
WHERE a.x = 'gotcha'

批量插入后获取生成的ID（用于后续关联） #

如果你的场景需要在插入后拿到这批数据的唯一ID来做后续关联，可以用OUTPUT子句把生成的ID捕获到表变量中，这样不需要额外的中间表：

-- 定义表变量存储生成的ID
DECLARE @GeneratedIds TABLE (GeneratedId INT)

CREATE TABLE #someTemp ( 
    id INT IDENTITY UNIQUE, 
    foo1 VARCHAR (20), 
    foo2 VARCHAR (80), 
    rand NUMERIC 
)

INSERT INTO #someTemp (foo1, foo2, rand) 
-- 把插入后自动生成的id输出到表变量
OUTPUT inserted.id INTO @GeneratedIds
SELECT v.foo_bar, v.bar_foo, v.list 
FROM #otherTemp a 
INNER JOIN some_table v ON v.id = a.id AND v.suu = a.ooo 
WHERE a.x = 'gotcha'

-- 后续操作可以直接用@GeneratedIds里的ID做关联
SELECT * FROM @GeneratedIds

补充说明 #

• 如果你确实有手动插入IDENTITY列值的需求（比如数据迁移），才需要执行SET IDENTITY_INSERT #someTemp ON，插入完成后再关闭SET IDENTITY_INSERT #someTemp OFF，但你的场景是要自动生成唯一键，所以完全不需要这一步。
• 尽量用SCOPE_IDENTITY()代替@@identity来获取单条插入的标识值，因为@@identity会返回当前会话中任何表的最后标识值，而SCOPE_IDENTITY()只返回当前作用域内生成的，更安全。

内容的提问来源于stack exchange，提问作者Vallier