Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
variable data

Answered variable data

  • Thursday, February 21, 2013 10:44 PM
     
     
    Sign In to Vote
    0

    hi all,

    I have a table A which has 4 cols

    ID col col1 col2 col3

    Now ID is created for relational purpose which is ok and col value is also fixed but col1,col2 and col3 are not fixed values. The values of last 3 columns can be changed. it can be daily , weekly or monthly.

    What I want that if I store ID to SomeOtherTable, I would like to remember what was the values of col1,col2 and col3 when a ID was stored in SomeOtherTable.

    What is the best way to do this? Dont want to add 3 cols along with ID in SomeOtherTable.

     

All Replies

  • Thursday, February 21, 2013 11:11 PM
     
     Answered
    Sign In to Vote
    0

    It appears to me the more appropriate way would be instead of updating the col2, col3, col4 values, you could append them as new rows and this will have new ID values(assuming ID column is the primary) and you can put this ID value as reference in the second table and you can look backup both current and past values using ID field. I think this is better approach, since you anyways need to log or store the col2,col3,col4 information somewhere, if not in this table. Atleast this approach would reduce the need to look up between 2-3 tables and if you want to narrow down to the exact difference milli seconds in the timestamp might play a bit...

    if you do not want to do above way,

    I think you would need a Timestamp Column in the "someothertable" that refers to when ID values was inserted/updated and you would need some kind of logging either through triggers or CDC to capture the changes in the col1,col2,col3 values along with Timestamp and ID field. you can later use this timestamp and Id to look back the col1,Col2,Col3 values.

    Hope it Helps!!




    • Edited by Stan210 Thursday, February 21, 2013 11:30 PM
    • Marked As Answer by Me.Saqib Wednesday, February 27, 2013 8:48 PM
    •  
     
  • Friday, February 22, 2013 12:03 AM
     
     
    Sign In to Vote
    0

    Hi there,

    Appending may not be suitable to you as you might be updating based on these id and col values.

    i would do in the following way:

    as you want to maintain the initial values, where do you want to keep those values?

    1) create a new table same like existing one with out any references.

    2) fire a trigger when you insert data into the original table to insert same records in to your new table.

    so any updates in your table will not effect your new table.

    cheers

    kumar

     
  • Friday, February 22, 2013 12:09 AM
     
     
    Sign In to Vote
    0

    +1 for Stan210's design.  It's so common in data warehouse design that it has a special name.

    David

    David http://blogs.msdn.com/b/dbrowne/