0

I am trying to do an OLE DB Command to add rows to my table only when the primary key field does not already exist within the same table. This is what I have so far:

insert into employee
   (employee_id, first_name, middle_initial, last_name)   /*fields of the employee table*/
   values (employeedID, firstName, mInitial, lastName)    /*columns from my input       */

 /* only insert into the table where employee_ID is not already in the table */
 where ((select employee_id from employee where employee_id = employeeID) = NULL);

Basically all I want is a conditional insertion statement.

Thanks!

CodeKingPlusPlus
  • 15,383
  • 51
  • 135
  • 216
  • Is there a reason you don't use the Lookup Transformation prior to your destination to weed out existing rows? – billinkc Jun 15 '12 at 14:32

1 Answers1

1

I'm not exactly sure how your package is set up but you might consider using a staging table that doesn't have any constraints. Insert all your records into there first and then make a statement at the end that looks like:

insert into employee (employee_id, first_name, middle_initial, last_name) 
select t.employee_id, t.first_name, t.middle_initial, t.last_name
from temp_employee AS t
left join employee ON t.employee_id = employee.employee_id
where employee.employee_id is null
Levi W
  • 805
  • 6
  • 13