0

This one is tricky to me in the since of SQL. I have 4 Tables. I'm having more a logic issue than anything else I think. I have a table called Vehicle. Within my application, any record changed in this Vehicle table gets inserted into a VLog table. The Primary key for Vehicle is VehicleID and it is related to VehicleID in VLog. In SQL, I'm not positive how to archive both of these tables. I have two archive tables. VehicleArchive and VLogArchive. Within my application, I'm able to "Archive" certain records based on a certain parameter. This was easy as I was using a gridview and could verify the VehicleID and Insert any record with that VehicleID into VLogArchive and VehicleArchive. However, I'm going to be dealing with live records and was wondering if there is a solution in SQL. There are multiple records for each VehicleID in VLog as VLog keeps track of all the changes made in Vehicle. Within my application itself, On "Update" button click, I was able to Insert the records with from VLog into VLogArchive by comparing it to the VehicleID within the gridview and then removing said records from VLog and the Location was changed to "File Room." It then goes on to insert the records from Vehicle into VehicleArchive also by comparing it to the VehicleID within the gridview again as long as the Location is "File Room." It seems backwards but I had to do it this way because if I tried to remove a record from Vehicle before removing the related records from VLog, it wouldn't delete as it is related. I do not know how to do this approach in SQL and was wondering if someone knew how to go about moving all the records at the same time if the Location is in "File Room."

I have found this and this but I'm not positive these are the approaches that I need. Thanks for the help!

Community
  • 1
  • 1
Humpy
  • 2,004
  • 2
  • 22
  • 45

2 Answers2

2

Don't do it like that; add an archived int(1) field to the tables and use that to filter the views.

Vehicle

then use this as the source for the gridview

SELECT * FROM Vehicle WHERE Archived=0

to make a record archived you can then do

UPDATE Vehicle SET Archived=1 WHERE ID=1

Moving records like this on a production system is never going to be easy and will almost certainly cause more problems than you can imagine.

If you do really want to do this; then you're going to have to do it record by record; by copying the data; and of course this is going to break the referential integrity that you'll have to fixup manually if any other tables reference Vehicle.

You can do this either in code as a background procedure or (better) using SQL / SSIS on the db server

Richard Harrison
  • 19,247
  • 4
  • 40
  • 67
  • Yes, in my archived tables, I do use ArchiveID. I'm just not sure how to move the records within SQL Server all at once, if possible to the archived tables. I know I can do it manually in the application itself by re-updating all the records that are in "File Room" but I was hoping to leave the manual process out of it. – Humpy Oct 29 '13 at 13:47
2

I'm going to disagree with @Richard. I'm always going to lean towards keeping your tables 'lean and mean' if at all possible. If the data is old / unused, get it out of your production tables! Keeping unnecessary records in your transactional tables presents many performance and maintenance risks. Since you're going to include a check on that flag in EVERY select query you run (using the view that @Richard suggests), you're going to have to add that flag to every nonclustered index to avoid tipping point problems. You're also going to run into problem with unique constraint enforcement (if your database is properly designed) as well as making queries more error prone (what if someone writes a report and doesn't use the view?). The list of problems with metadata flags like that go on and on and on. Just don't do it.

There are a lot of different ways to peel the onion on archiving data. I prefer more frequent and smaller transactions myself. If you're archiving to the same server, use T-SQL as in your first link. If you're archiving to a different server, use SSIS.

Jon Boulineau
  • 451
  • 2
  • 7
  • I'm not in disagreement with this approach - it's a balance between frequency of operation (of archive) and data sizes. I generally tend to favour the simplest approach first and refine later. There are disadvantages to archiving off data (complexity, ability to include in historic reports etc); that means it needs careful consideration before implementing a different table strategy. Delete or move the minimum; unless you've got tens of millions of records and need to query across all of them. – Richard Harrison Oct 29 '13 at 16:06