Append Rows to Table That Don't Match on ID Column Without Duplicating-CodePudding

I have Two tables that contain employees.

One table has all active employees (Current_Employees) and the other one has employees that have been joined in the last month (Greenhouse_Employees). Sometimes these employees overlap.

I have a unqiue ID (position_ID) that I want to do the following with in the abstract:

If Greenhouse_Employees unique ID exists in or matches to an ID in Current_Employees, ignore, if it does not: append it and its associated columns to the table

Not all of the columns match in either table, some do.

The code below almost works, but if there is a single inconsistency any any column I coalesce, it duplicates the row: (Some employees have inconsistent [loc] (locations) in the tables due to data entry error

SELECT 

       COALESCE(#CURRENT_EMPLOYEES.[employee_status], [GREENHOUSE_TABLE].[job_status]) AS employment_status
      ,COALESCE(#CURRENT_EMPLOYEES.[employee_id], 'N/A') AS employee_id
      ,COALESCE(#CURRENT_EMPLOYEES.[employee_name], CONCAT([GREENHOUSE_TABLE].[first_name],' ',[GREENHOUSE_TABLE].[last_name])) AS employee_name
      ,COALESCE(#CURRENT_EMPLOYEES.[hire_date], [GREENHOUSE_TABLE].[hire_date]) AS hire_date
      ,COALESCE(#CURRENT_EMPLOYEES.[salary], [GREENHOUSE_TABLE].[salary]) AS salary
      ,COALESCE(#CURRENT_EMPLOYEES.[bonus_percent], [GREENHOUSE_TABLE].[annual_bonus]) AS bonus_percent
      ,COALESCE(#CURRENT_EMPLOYEES.[commission_percent], '0') AS commission_percent
      ,COALESCE(#CURRENT_EMPLOYEES.[currency], 'N/A') AS currency
      ,COALESCE(#CURRENT_EMPLOYEES.[company_title], [GREENHOUSE_TABLE].[company_title]) AS company_title
      ,COALESCE(#CURRENT_EMPLOYEES.[company_department], [GREENHOUSE_TABLE].[company_department]) AS company_department
      ,COALESCE(#CURRENT_EMPLOYEES.[country], 'N/A') AS country
      ,COALESCE(#CURRENT_EMPLOYEES.[loc], 'N/A') AS loc
      ,COALESCE(#CURRENT_EMPLOYEES.[job_level], [GREENHOUSE_TABLE].[job_level]) AS job_level
      ,COALESCE(#CURRENT_EMPLOYEES.[kamsa_code], [GREENHOUSE_TABLE].[kamsa_code]) AS kamsa_code
      ,COALESCE(#CURRENT_EMPLOYEES.[position_id],[GREENHOUSE_TABLE].[position_id]) AS position_id

FROM #CURRENT_EMPLOYEES

FULL JOIN [Headcount].[dbo].[greenhouse_employees] AS GREENHOUSE_TABLE ON #CURRENT_EMPLOYEES.[position_id] = [GREENHOUSE_TABLE].[position_id]

ORDER BY #CURRENT_EMPLOYEES.[hire_date] ASC

CodePudding user response：

I believe you need to You need to INSERT ... SELECT ... WHERE NOT EXISTS(...) or INSERT ... SELECT ... WHERE <Id> NOT IN (...). Something like:

INSERT #CURRENT_EMPLOYEES (employee_status, employee_id, ...)
SELECT employee_status, employee_id, ...
FROM Headcount.dbo.greenhouse_employees GE
WHERE NOT EXISTS (
    SELECT *
    FROM #CURRENT_EMPLOYEES CE
    WHERE CE.employee_id = GE.employee_id
 )

The other form is

INSERT #CURRENT_EMPLOYEES (employee_status, employee_id, ...)
SELECT employee_status, employee_id, ...
FROM Headcount.dbo.greenhouse_employees GE
WHERE GE.employee_id NOT IN (
    SELECT CE.employee_id
    FROM #CURRENT_EMPLOYEES CE
 )

Both assume that employee_id is unique in greenhouse_employees.