Trend Rolling Table

  • Development has created 7 day rolling tables on multiple servers. I want to pull this data over to another table so I can trend daily averages over a few months. The rolling table is populated throughout the day meaning data is added/removed every few minutes. What would be the best method to copy this data to my table without missing or creating duplicate rows?

  • Debating on using replication or SSIS Lookup.

  • Erin-489205 (12/6/2012)


    Development has created 7 day rolling tables on multiple servers. I want to pull this data over to another table so I can trend daily averages over a few months. The rolling table is populated throughout the day meaning data is added/removed every few minutes. What would be the best method to copy this data to my table without missing or creating duplicate rows?

    Step 1 would be to find out how they are deleting the data so you can intercept it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Replication could be problematic as SQL will replicate the DELETEs as well as the INSERTs.

    Also, do you need to capture UPDATE activity or just INSERTs?

    Finally, are you on Enterprise or Standard Edition?

    CDC could work well for this if you are on Ent Ed (unfortunately, CDC is not available on Std Ed).

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • SSIS lookup is working well.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply