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

DECLARE @Employee TABLE (EmployeeID int, EmployeeName varchar(200), ScreenOrder int)

DECLARE @TempEmployee TABLE (EmployeeID int, EmployeeName varchar(200))
INSERT INTO @TempEmployee VALUES (1, 'Name 1')
INSERT INTO @TempEmployee VALUES (2, 'Name 2')
INSERT INTO @TempEmployee VALUES (3, 'Name 3')
INSERT INTO @TempEmployee VALUES (4, 'Name 4')
INSERT INTO @TempEmployee VALUES (5, 'Name 5')

INSERT INTO @Employee (EmployeeID, EmployeeName, ScreenOrder)
SELECT TempEmployee.EmployeeID, TempEmployee.EmployeeName, 0
       FROM @TempEmployee TempEmployee
LEFT JOIN @Employee Employee ON TempEmployee.EmployeeID = Employee.EmployeeID
WHERE Employee.EmployeeID IS NULL

DECLARE @ScrOrder int
SELECT @ScrOrder = MAX(ScreenOrder) FROM @Employee 

UPDATE @Employee SET ScreenOrder = @ScrOrder,
                     @ScrOrder = @ScrOrder + 1
WHERE ScreenOrder = 0
SELECT * from @Employee

INSERT INTO @TempEmployee VALUES (7, 'Name 7')
INSERT INTO @TempEmployee VALUES (8, 'Name 8')
INSERT INTO @TempEmployee VALUES (9, 'Name 9')
INSERT INTO @TempEmployee VALUES (10, 'Name 10')
INSERT INTO @TempEmployee VALUES (11, 'Name 11')

INSERT INTO @Employee (EmployeeID, EmployeeName, ScreenOrder)
SELECT TempEmployee.EmployeeID, TempEmployee.EmployeeName, 0
       FROM @TempEmployee TempEmployee
LEFT JOIN @Employee Employee ON TempEmployee.EmployeeID = Employee.EmployeeID
WHERE Employee.EmployeeID IS NULL

SELECT @ScrOrder = MAX(ScreenOrder) FROM @Employee 

UPDATE @Employee SET ScreenOrder = @ScrOrder,
                     @ScrOrder = @ScrOrder + 1
WHERE ScreenOrder = 0
SELECT * from @Employee