I have two tables: Employee and TempEmployee. TempEmployee comes from another source and has an ID field, and EmployeeID field, and an EmployeeName field. Employee has those same fields plus a ScreenOrder field.

I want to come up with a statement that will insert the record into Employee if it doesn't already exist but does exist in TempEmployee as keyed by EmployeeID. I want the ScreenOrder field to always be the maximum value + 1.

I can show the records like so:

SELECT * FROM TempEmployee WHERE EmployeeID NOT IN (SELECT EmployeeID FROM Employee)

but I don't know how to insert those records into Employee without creating a temporary table and looping through it. Is there an easy way I don't know about?

Thanks!

(Sorry about the thread title - I forgot to rename it to something descriptive after I typed the message)