Skip to content
Advertisement

SQL Select in an Insert statement

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')

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement