sql查询将值插入到自动递增列

Question

您好，当从另一个表插入一个表的值时，我想填充自动增量列的值。下面是我正在使用的查询，它会抛出错误

create table test12
(
Id int,
name varchar(255),
dept varchar(255)
)

insert into test12 values(1,'f','cs'),(2,'b','cse'),(3,'c','cs'),(4,'d','cse'),(5,'e','cs'),(6,'f',null)

select * from test12                                                            

create table test34
(
seq int identity(1,1) not null,
name varchar(255) not null,
dept varchar(255) default('cs')
)
insert into test34(seq,name,dept) values 
(1,(select name from test12),
(select case when dept='cse' then 'Y' else 'N' end as dept from test12))          

请问哪里错了

Answer 1

您无需为标识列提供值，它是自动添加的，只需排除标识列形式插入为:

INSERT INTO test34 (name, dept) 
SELECT 
    name,
    CASE WHEN dept = 'cse' THEN 'Y' ELSE 'N' END AS dept 
FROM test12

如果您真的想手动添加标识值，请尝试以下 SET 语句。

SET IDENTITY_INSERT test34 ON;

INSERT INTO test34 (seq, name, dept) 
SELECT 
    ID
    name,
    CASE WHEN dept = 'cse' THEN 'Y' ELSE 'N' END AS dept 
FROM test12

SET IDENTITY_INSERT test34 OFF;