I have some huge tables which have around 10years data, and each table has approx 10000000+ rows.
Basically, I want to remove older than 2 years data and make a copy of deleted data in a separate table(I'll export the separate table to CSV and drop it later)
I found this great article: Archive huge record
I want the same thing, but I don't have an ID column in my tables.
So the process should like this,
Insert a single row in my separate table, then delete the row from the main table; commit.
Then, repeat the same step.
Its my main production table and its always busy, so no locks, blocks, and deadlock.
Can anyone suggest a better approach for this?
I have already asked a question about archival, but here I don't want to partition the table.