Help needed in capturing change data

  • I have a table table1 that I truncate and enter new and existing records everyday from source table. Source table keep getting appended which contains students Act, SAT scores. I check a criteria based on ACT and SAT scores and enter the data In table 2 if they are eligible for certain program or not.

    The issue is when a student comes in with the new ACT or SAT score, I need to check if they become eligible with the new scores or not if they were not eligible in the past

    Since I truncate and reload table1, I was thinking to create a temp table with the copy of past day's table1 data and then check if the scores has been changed or not.

    I am not if this is the best approach And how can I query this.

    Please help.

    Thanks,

    Blizzard

  • rather than copy data between tables do one of the following:

    either have an additional column in the table with the datestamp to identify the version of the record, OR

    Create two tables with the same structure (TableA, TableB) and two synonyms (TableCurrent,TableHistoric) and in your procedure, truncate the table with the oldest data (should be 2 days old), redeclare the synonyms so that TableCurrent is the empty table and TableHistoric contains yesterdays data then load the data into TableCurrent.

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

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