I've been dealing with million of data deletion in day to day process. Basically I have 4 tables.
Table_A
Table_B
Table_C
Table_D
I'm attempting to delete data older than 10 days in all tables.
Possibly I will be deleting around a million in each table. I have created a stored procedure to do these operations.
The steps I have followed to delete the data is
Step 1: Move the recent days (data which I have to preserve) to a temp table
select * into Table_A_Temp
from Table_A
where <<where clause last 10 days to till date>>
Step 2: Rename main table to old table (table with all days data)
exec sp_rename 'Table_A', 'Table_A_Old'
Step 3: Rename temp table to main table (table with data between last days to till date)
exec sp_rename 'Table_A_temp', 'Table_A'
Step 4: Query the temp table with time frame if any new data is inserted during the copy process
Insert into Table_A
select * from Table_A_old
Step 5: Drop old tables
DROP TABLE Table_A_old
Step 6: Create keys and constraints in main table (means renamed table)
code to create primary keys and constraints
Problem: If I continuously inserting data into table while the store procedure is running I'm losing data for few seconds. (all 4 tables)
Case 1: While renaming table
when I rename the main to old and temp table to main
I'm getting invalid object error (that table is exist error)
Case 2: Two of my tables have foreign key relation If I insert data before creating constraints and key I'm getting related errors.
How to handle and delete the data properly without losing data. Please advice the best practices.