CODEWITHSUNDEEP
sql-server
Mike the Gardener
Mike the Gardener

Reputation: 105

SQL Insert using a Where clause

I am trying to write a query that will only insert data if the data does not exist in the table already. Here is my query:

INSERT INTO JobTable (JobTitleName)
VALUES ('PROGRAMMER') 
WHERE JobTitleName NOT EXISTS(SELECT JobTitleName FROM
JobTable x WHERE x.JobTitleName = 'PROGRAMMER')

I am getting an error on the "where" clause. I am sure the answer is easy, I am simply drawing a blank. Any help would be greatly appreciated.

Upvotes: 0

Views: 43

Answers (2)

Guffa
Guffa

Reputation: 700342

You can't use a where clause in an insert, but you can use a query as source for the insert, and that can have a where.

Join in the JobTable with a left join so that you can make the query return zero records if the job title already exists:

insert into
    JobTable (JobTitleName)
select
    x.Title
from
    (select 'PROGRAMMER' as JobTitleName) x
    left join JobTable j on j.JobTitleName = x.JobTitleName
where
    j.JobTitleName is null

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Use an IF statement to test for the existence of the value in question.

IF NOT EXISTS(SELECT JobTitleName 
                  FROM JobTable x 
                  WHERE x.JobTitleName = 'PROGRAMMER')
    INSERT INTO JobTable 
        (JobTitleName)
        VALUES 
        ('PROGRAMMER');

Upvotes: 3

Related Questions