Hi All,
I have 20 tables to be archived. Am moving them to archive database from production and deleting from production. They have up to 3 billion rows.
Am using transaction and while loop with @@rowcount. Sample code would be like this.
WHILE 1 = 1
BEGIN
BEGIN TRANSACTION
DELETE FROM A WHERE...
DELETE FROM B WHERE...
DELETE FROM C WHERE...
IF @@ROWCOUNT = 0 BREAK
COMMIT
END
Since am doing in a hierarchy (because am using ID) and table C is comparatively small, @@ROWCOUNT returns 0 after first execution the WHILE loop ends. How to run it effectively? Is there any alternate way to do this in a more feasible way?
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂