SQL NOT IN Function

Question

Iam trying to insert a record and i want to check that it is not already present in the table.

I try

INSERT INTO emp (empno, name) 
VALUES(2, 'ram') 
WHERE empno NOT IN (select empno from emp);

but it shows error 'incorrect syntax near where'

Answer 1

You can use following query to insert records into emp If you are inserting one record at a time then following query will work as best as it can ...

insert into emp (empno,empname)
    select distinct empno,empname
      from ( 2 empno, 'ram' empname ) as a 
     where  a.empname not in ( select empname from emp )

If you are willing to insert multiple records then just find below query

insert into emp (empno,empname)
    select max(empno),empname 
      from ( select 2 empno, 'ram' empname 
              union 
             select 3 empno, 'ram1' empname 
              union 
             select 4 empno, 'ram' empname 
           ) as a 
     where a.empname not in ( select empname from emp )
     group by empname