insert from one table to 2nd if row already exists

brocktune · May 14th, 2007, 12:32 PM

I have a table that contains employee data pulled from another database. I want to take the data in this table (EmpID, Last_promotion_date) and use it to insert into a 2nd table (EmpID (char), Last_promotion_date (datetime), Emp_row_Number (int)). Using the data from the first table, if a row does not exist in table 2 for a given EmpID, insert a row. However, if a row does exist, where the Last_promotion_date is different from the row coming from table one, insert a new row for that EmpID, with the new Last_promotion_date value, but with a Emp_row_Number value incremented by one over the last row for that EmpID. Anyone have any idea on how to do this with Stored Proc/Triggers?

Thanks in advance

rstelma · May 14th, 2007, 03:06 PM

Just a suggestion. You might want to take a look at the application logic elsewhere. For instance, when a person gets a promotion, on that 'Save' add another row to the second table. That way you always know that row will be in the second table. You also might want to look at why that second table would exist if you already have the data in another table.

happygv · May 15th, 2007, 12:56 AM

Code:

INSERT INTO 2ndTable SELECT 1stTable.EmpID, 1stTable.Last_promotion_date, MAX(2ndTable.EmpID) + 1 
    FROM 1stTable, 2ndTable 
    WHERE 1stTable.EmpID <> 2ndTable.EmpID OR 
1stTable.Last_promotion_date <> 2ndTable.Last_promotion_date

I am not sure if you need to use OR or AND operator there, since I am not clear if you wanted to do it as separate inserts or as single insert. I think you should decide on that. Moreover, you didn't mention what should go into Emp_row_Number when EMPID doesn't match between those 2 tables in question, though I used MAX EMPID + 1 for that too.

Hope that helps.
Cheers

_________________________
- Vijay G
Strive for Perfection