Reputation: 1455
How can I combine these two scripts? I basically don't want to hard code the number 34
in Query 2
. I want the script to take the next number of job_id. Query 1
returns 34
, so Query 2
should take 35
as the job_id
.
Query 1
select top 1 job_id from job
order by job_id desc
Output of query 1 - 34
Query 2
insert into job (job_id, name)
values (35, 'Defend them')
Upvotes: 0
Views: 46
Reputation: 164234
There is no need to use order by job_id desc
and top 1
to get the value you want.
You need max(job_id) + 1
insert into job (job_id, name)
select max(job_id) + 1, 'Defend them'
from job
Upvotes: 0
Reputation: 383
select top 1 job_id, 'Defend them'
into job (job_id, name)
from job
order by job_id DESC
Upvotes: 0
Reputation: 1271231
You do this by defining job_id
as an identity
column:
create table jobs (
job_id int identity(1, 1) primary key,
name varchar(255)
);
Then you can just do:
insert into jobs (name)
values ('Defend them');
And SQL Server assigns the job id.
Upvotes: 2
Reputation: 7184
How about this?
insert into job (job_id, name)
select top 1 job_id+1, 'Defend them'
from job
order by job_id desc
Upvotes: 0