SQL Server : update existing record and insert new record

Question

I have a table containing a set of temporary workers who's contract periods can be indefinite and can get renewed not different departments. Upon renewal to a different department, the end date needs to be updated and the start date needs to be the end date plus 1 day.

Current output:

+---------------------+------------------+------------------+------------------+
|         Name        |    Department    |    Start Date    |     End Date     |
+---------------------+------------------+------------------+------------------+
|         Tom         |     Finance      |    2010-08-09    |   9999-12-31     | 
+---------------------+------------------+------------------+------------------+

Desired output

+---------------------+------------------+------------------+------------------+
|         Name        |    Department    |    Start Date    |     End Date     |
+---------------------+------------------+------------------+------------------+
|         Tom         |     Finance      |    2010-08-09    |   2010-10-10     |
|         Tom         |        HR        |    2010-10-11    |   9999-12-31     | 
+---------------------+------------------+------------------+------------------+

Currently, I take the existing records and save them into a temp table. I then do a join on multiple fields to the existing table to see if this person exists before. If the person exists then I proceed with saving the data into the temp table and performing the updates before going back and updating the main table. I don't like this solution and it's very convoluted. Is there any better solution to resolve the above problem?

Answer 1

EDIT. Since there is already a stored procedure, you can embed similar logic into the SP. I created a sample stored procedure that will take an employee name, update the department, and then implement your business logic. Again, target a true primary key field (not the employee name) as a parameter or you might end up updating multiple records.

ALTER PROCEDURE UpdateDepartment @EmpName VARCHAR(20), @Dept VARCHAR(20)
AS
BEGIN
    DECLARE @Changed TABLE
    (
        EmpName VARCHAR(20),
        OldDepartment VARCHAR(20),
        NewDepartment VARCHAR(20),
        StartDate DATETIME2,
        EndDate DATETIME2
    )

    UPDATE TempEmployees
    SET
        Department = @Dept
    output 
        inserted.EmpName, 
        deleted.Department, 
        inserted.Department, 
        inserted.StartDate,
        inserted.EndDate
    into @Changed
    WHERE EmpName = @EmpName

    DECLARE @OldDept VARCHAR(20);

    SELECT @OldDept = OldDepartment FROM @Changed

    UPDATE TempEmployees 
    SET 
        EndDate = CURRENT_TIMESTAMP,
        Department = @OldDept
    WHERE EmpName = @EmpName;

    INSERT INTO TempEmployees 
    SELECT
        EmpName,
        NewDepartment,
        DATEADD(DAY, 1, CURRENT_TIMESTAMP),
        '99991231'
    FROM @Changed

END

---

One option is to use a trigger, and take advantage of the output tables that are generated by the UPDATE statement. When an UPDATE statement is run, an inserted and a deleted output table are generated detailing the before state and after state of the record.

See the code below, and try to modify it for your application. Do note, this current version of the trigger uses the Employee Name as part of the predicate for the updates. If you have more than one record for a specific employee name, they will all be updated. So, it makes better sense to use a true primary key field on the table, ensuring that only one record is updated.

CREATE TRIGGER TempEmployeeMovedDepartment
ON TempEmployees
AFTER UPDATE 
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(Department)
    BEGIN
        --Update the End Date for the Current Record. In this case, I'm using the EmpName
        --field to denote the record to update. Presumably, you'd have an actual Primary Key
        DECLARE @PK VARCHAR(20);
        DECLARE @OldDept VARCHAR(20);

        SELECT @PK = EmpName, @OldDept = Department FROM deleted;

        UPDATE TempEmployees 
        SET 
            EndDate = CURRENT_TIMESTAMP,
            Department = @OldDept
        WHERE EmpName = @PK;

        INSERT INTO TempEmployees 
        SELECT
            EmpName,
            Department,
            DATEADD(DAY, 1, CURRENT_TIMESTAMP),
            '99991231'
        FROM inserted
    END
END