Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2009-03-05 : 22:28:56
|
| currently we truncate data in a table before loading the legacy data into the table. since we are truncating the data before loading, we do'nt have any history information for that table. What's the best way of doing this, without truncating the data as we want to keephistory information in the table. Thanks! |
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2009-03-05 : 23:05:49
|
This is more of a business question than a technical one. You could keep a single archive table and populate it using INSERT ArchiveTable SELECT * FROM OriginalTable or something similar. This assumes that you need the data readily available in the database - if you just need an audit trail of the data that was deleted, you could write out the data to an output file to store external to the database. Of course, you could always do a full database backup just before the truncate and load, but this may be overkill unless you're addressing a large number of tables in this manner.---------------------Tim Mitchellwww.BucketOfBits.com |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2009-03-06 : 15:18:21
|
| Thanks for your response, actually we want to track the changes that are being done to an order, like who changed it, order_status, etc, so saying that which one will be the best way of doing this among the following:1. Trigger2. Inserting into a archieve table before truncating the original table3. Writing the data to an output file(before truncate) to store external to the databaseThanks for all the responses. |
|
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2009-03-06 : 21:50:15
|
| Without knowing how you will use this archived data, any suggestions are just shooting in the dark... How often do you need access to archived data? Do you even need to archive/truncate this data, or can it be kept in the original table?As a general rule, if you need regular access to the information, keep it in the database. Write it to an archive table if you must, but if you have to do so, most situations are better served doing archive operations in batch. Triggers have their place but don't seem like the solution here.hth,Tim---------------------Tim Mitchellwww.BucketOfBits.com |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2009-03-09 : 14:26:52
|
| Actually there is a scheduled report that uses this data, only when the user complaints about the report not picking up correct data only then we need this archieved data for troubleshooting purpose to see if anything has changed on the order etc.. Please let me know what's the best way of doing this.Thanks. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2009-03-10 : 14:15:28
|
| Please advice! Thanks!! |
|
|
|
|
|
|
|
|
maintain history data of a table