I'm stuck trying to figure a single statement SQL syntax for the
following process (ElevateDB 2.x):

insert into TempEmployee (PolicyID, EmpID, LastName, FirstName)
 Values (select 9, EmpID,  LastName, FirstName
 from Employee
 where not EmpID in (select EmpID from EmpBenefit
        where ReasonID = 18))

EmpBenefit contains a list of policies assigned to employees.  If no
entry is found in that table, I want to add an entry into TempEmployee
from the Employee table.  So I'm trying to generate an exclusion list first.

I'm getting a "a scalar query can only return a single value" error with
the query, so I know it needs to be adjusted, but I'm not sure how.  I'd
rather not do this as a stored procedure if I can help it.

Any help would be appreciated.
--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon,

You need to take out the word VALUES from the statement.  

Try this:

insert into TempEmployee (PolicyID, EmpID, LastName, FirstName)
 select 9, EmpID,  LastName, FirstName
 from Employee
 where not EmpID in (select EmpID from EmpBenefit
        where ReasonID = 18)


Richard Harding

Richard Harding wrote:
>
> You need to take out the word VALUES from the statement.  
>
> Try this:
>
> insert into TempEmployee (PolicyID, EmpID, LastName, FirstName)
>   select 9, EmpID,  LastName, FirstName
>   from Employee
>   where not EmpID in (select EmpID from EmpBenefit
>          where ReasonID = 18)

Thanks, that worked perfectly.  I hate when I miss the obvious...

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com