CODEWITHSUNDEEP
t-sql
J Ames
J Ames

Reputation: 63

T-SQL, Get distinct values from column in source, check target, insert if not exist

I've seen several somewhat similar questions, but nothing exactly like mine. A T-SQL god should be able to answer this is a flash.

Source table (feeder) of employees and department codes from HRMS system feed. I have an employees table and a departments table in my SQL Server database. I need to have a stored proc that will first get a distinct list of department codes in the feeder table, then check those codes to see if they exist in the departments table. If they don't then insert into the departments table from the feeder table. Then, do the insert into the employee table.

Right now I have found that one of the business analysts has been getting separate list of departments in Excel and adding them manually. Seems crazy when the data is already coming into the feeder table from HRMS. I can do the inserts, but I don't know how to loop through feeder table to see if the department code in each row exists in the departments table already. Thanks for any assistance.

J.

Upvotes: 0

Views: 148

Answers (2)

JAQFrost
JAQFrost

Reputation: 1431

Merge will work. Since we're just doing inserts though, this will too: 

INSERT 
    department
    ( 
       departmentCode
    )
SELECT departmentcode 
FROM 
    ( 
        SELECT 
            departmentcode 
        FROM 
            feeder
        EXCEPT 
        SELECT 
            departmentcode
        FROM 
            department
    ) c;

With indexes on departmentcode in both tables, this pattern usually performs well.

Upvotes: 0

wtjones
wtjones

Reputation: 4160

You can use the merge keyword in SQL 2008 or greater. I just tested this snippet:

merge department as d
using feeder as f on f.code = d.code
  when not matched then
    insert (code)
      values (f.code);

Upvotes: 1

Related Questions