在插入语句中选择 SQL 选项

Question

如何

组合这两个脚本？我基本上不想在Query 2中硬编码数字34。我希望脚本采用下一个job_id数。 Query 1返回34，所以Query 2应该把35作为job_id。

查询 1

select top 1 job_id from job
order by job_id desc

查询 1 - 34 的输出

查询 2

insert into job (job_id, name)
values (35, 'Defend them')

Answer 1

通过将job_id定义为identity列来执行此操作：

create table jobs (
     job_id int identity(1, 1) primary key,
     name varchar(255)
);

然后你可以做：

insert into jobs (name)
    values ('Defend them');

SQL Server 分配作业 ID。

Answer 2

无需使用 order by job_id desc 和 top 1 来获取所需的值。
你需要max(job_id) + 1

insert into job (job_id, name)
select max(job_id) + 1, 'Defend them'
from job

Answer 3

这个怎么样？

insert into job (job_id, name)
select top 1 job_id+1, 'Defend them'
from job
order by job_id desc

Answer 4

select     top 1 job_id, 'Defend them' 
into       job (job_id, name)
from       job
order by   job_id DESC