DECLARE @ROWCOUNTER AS INT;
SET @ROWCOUNTER = 903843;
WHILE @ROWCOUNTER != 0
DELETE TOP (1000)
OUTPUT DELETED.[ITEMNMBR], DELETED.[CURNCYID], DELETED.[PRCLEVEL], DELETED.[UOFM], DELETED.[TOQTY], DELETED.[FROMQTY], DELETED.[UOMPRICE], DELETED.[QTYBSUOM], DELETED.[DEX_ROW_TS] INTO IV00108_History
WHERE PRCLEVEL IN ('LIST');
SET @ROWCOUNTER = @@ROWCOUNT;
--select * from iv00108_history
Create a history table with the same schema as the live table (might want to remove the indexes to keep size down).
Use the above script, supplying the price list name to move the prices from the live IV00108 table to the new custom table IV00108_history.
This moves the records in batches of 1000 to keep the transaction locking under control in the database. If too much information is locked (selected) we start going from row locks to page locks into table locks. Performance goes at and users start to complain at that point. The price table has three million rows in it so this is a wise precaution.
Quality and finance audits
In your annual audits, as a software manager the question always crops up of how is work in progress protected? All the project source code is normally safely locked away in team foundation server. The Team Foundation Server is in turn backed up and backup shipped off site. What about drive failure on the local developer machine? Normally hours or days of work could be lost since the last check was performed!
My answer to this is to use Auto Shelve extension and a weekly machine backup of my development machines.
Auto Shelve protects work loss from drive failure since last checkin
Auto shelve is a Visual Studio extension that periodically & automatically shelves your pending changes into source control as you work. It does this in background and so has minimal impact. Should my SSD drive fail catastrophically, work since my last check in will have been preserved in a shelf set in the team foundation server.
After setting it up, Auto Shelve keeps overwriting the shelf set with the latest pending changes. After using it for some time now I can say it is effortless backup and I love it. I will love it even more when the day comes that I have a disaster on my hands.
Install it through Tools>>Extensions and Updates
Search on the online node to the left, for autoshelve and install.
Go to the settings to set up your shelf set name and the frequency of shelving.