CODEWITHSUNDEEP
sqlsql-serversql-server-2000
nav100
nav100

Reputation: 411

Delete data from database older than two years

How can I delete the data from database(all tables) older than two years? I am using SQL server 2000. Please advise. I am not sure about foreign constraints.

Upvotes: 1

Views: 1481

Answers (2)

Marcin
Marcin

Reputation: 1615

You need some timestamp, based on which you can delete records. Then you can use Scheduled Tasks to fire queries which delete old data.

Upvotes: 0

duffymo
duffymo

Reputation: 309008

You'll have to delete records in a certain order if there are foreign key constraints. You have to delete the "many" records that refer to "one" before you can delete "one".

It'll be a multi-step process:

  1. Find all the primary keys in the "one" table using a timestamp (required) that's older than the cutoff date
  2. Delete all the records from the "many" table whose foreign key column value are in the set of primary keys from step 1; repeat for all "many" tables that point to this "one" table
  3. Delete all the records found in step 1 from the "one" table.
  4. Repeat for all "one" tables with timestamps.

Upvotes: 3

Related Questions