CODEWITHSUNDEEP
sql
Aaron
Aaron

Reputation: 1455

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

Upvotes: 0

Views: 46

Answers (4)

forpas
forpas

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

Andrew Castellano
Andrew Castellano

Reputation: 383

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

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

Steve Kass
Steve Kass

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

Related Questions