Using the Merge Statement to populate a historical table with Effective Dating

One of my favorite uses for the MERGE statement introduced in SQL Server 2008 is the updating of a historical table. With versions prior to 2008 this operation had to be performed in two separate statements. Merge helps us to streamline the process.

The advantage to the database engine when using a Merge statement is in it’s minimal locking. Prior to merge, we had to update the record if it existed. And if it did not exist, we inserted the record. Long story short, it was double the amount of locking before the optimizer knew which rows to exclusively lock.

To understand what’s going on in the statement below, you have to read starting from the MERGE statement. This is where the initial updating is taking place first. As you scroll down, you will see the OUTPUT clause. This actually outputs the results of the MERGE statment. You can think of this output as a virtual table named “merged”. The $action variable is an intrinsic column (if you will), that contains the action that row played during the MERGE. Lastly, if you scroll to the top, you will find the insert statement that finally inserts the new “current” record for the corresponding “expired” record we just retired. This was the same row we used for the criteria to UPDATE the expired row, however we did not use any of it’s values.

DECLARE @Now datetime = GETDATE()
DECLARE @EffToDate datetime = '2079-06-06T00:00:00.000'
DECLARE @JobID INT = 1 -- This would be dynamic

INSERT INTO Employee_History
(
     EmployeeID
    ,FirstName
    ,LastName
    ,EmploymentStatus
    ,EffFromDate
    ,EffToDate
    ,IsCurrentRecord
    ,JobID
)
SELECT
     EmployeeID     = merged.EmployeeID
    ,FirstName          = merged.FirstName
    ,LastName           = merged.LastName
    ,EmploymentStatus   = merged.EmploymentStatus
    ,EffFromDate        = merged.EffToDate
    ,EffToDate          = @EffToDate
    ,IsCurrentRecord    = 1
    ,JobID              = merged.JobID
FROM(
  MERGE Employee_History eh
  USING (
        SELECT
            EmployeeID
            ,FirstName
            ,LastName
            ,EmploymentStatus
            ,ChkSum = CHECKSUM(
                 FirstName
                ,LastName
                ,EmploymentStatus
            )
        FROM Employees e
    ) e
      ON eh.EmployeeID = pfs.EmployeeID
      AND eh.IsCurrentRecord = 1
  WHEN MATCHED AND
    eh.ChkSum != e.ChkSum -- Employee exists but a value has changed
    THEN -- Expire this record we have a new one
    UPDATE
        SET  eh.IsCurrentRecord = 0
            ,eh.EFFToDate       = @Now
  WHEN NOT MATCHED
    THEN -- Insert brand new employee
    INSERT
    (
        EmployeeID
        ,FirstName
        ,LastName
        ,EmploymentStatus
        ,EffFromDate
        ,EffToDate
        ,IsCurrentRecord
        ,JobID
    )
    VALUES
    (
        EmployeeID
        ,FirstName
        ,LastName
        ,EmploymentStatus
        ,@Now
        ,@EffToDate
        ,1 --This record is current (its the first)
        ,@JobID
    )
    OUTPUT
            $action
            ,INSERTED.EmployeeID
            ,INSERTED.FirstName
            ,INSERTED.LastName
            ,INSERTED.EmploymentStatus
            ,INSERTED.JobID
            ,INSERTED.EffToDate
) AS merged (
            output_action
            ,EmployeeID
            ,FirstName
            ,LastName
            ,EmploymentStatus
            ,JobID
            ,EffToDate)
WHERE merged.output_action = 'UPDATE'

There are however a couple pitfalls regarding the merge statement. First, it’s not easy to troubleshoot. Many times I will scratch my head wondering why a record didn’t get updated. It always ends up being an issue with what I have written, however because the statement is so complex, it is difficult at times to keep a grasp at what’s going on. The other big issue I have with the merge statement, is that I am unable to tell both how many records it has updated AND how many it’s inserted. Previously with the two statement method, I could select the @@ROWCOUNT afterwards. However now, I find I have to set special flags, the go back and count the flags to determine the updates vs the inserts.

3 comments
David 19 Mar 2012 at 10:55 am

Very nice use of the OUTPUT clause! I found this post when searching for how to do this exact thing: update a history table by closing out the old row and inserted the new row in one step. Thanks!

Sergio 29 Aug 2015 at 6:48 am

Excuse my ignorance, Andrejus.In the View Object (CgcompronabegastoView), I have to edit the attrtbuie Ejercicio, and in the area of “Query Column” complete the Expression of this with my Sql Query. I have to edit a property more?,probe this in the attrtbuie, and does not store my sql query in the area “Query Column” in the view Object (VO).Then, also edit the attrtbuie of the Entity, enable the check box: “Derived from SQL Expression” and update the “Expression” in the area “SQL Expression” in the SQL Query. This allows you to store the SQL query in the View object, the first idea previously described above.This configuration in the entity, results in the value of attrtbuie “Ejercicio” is the same as the result of my SQL query (Select max(Ejercicio) …..), running the AppModules. I’m not property or to modify and test values in the Entity / View, with this idea to solve this.I think your observation of work on the View, to obtain the solution is correct, does not probe with Groovy and such functions. Ej: #RowSetAttr.max(GroovyExpr), also could be a solution.For this particular case of Assignment of default values to an “Attribute Of An Entity”, as the result of an SQL query, I did not find much information, only referenced in Chapter 6: Specifying a Business Domain, How Do You Implement Attribute defaulting and Calculation? – Oracle Jdeveloper 11 – HandBook. I ask if you remember such information or referred to such Assignment of default values. from already thank you very much

SK 27 Apr 2011 at 11:29 pm

hi ben,I have loaded data for 1 to 20 of the month…in target DB using Merge statement. and now a restatement of file on day 10 should occure.How to insert/update the new vaules and should we delte the records on that day10 ?how we should implement REstatement of Data using Merge statemnt?

Featured Articles

 Site Author

  • Thanks for visiting!