Removing certain records of a SQL Server table

Question

Removing certain records of a SQL Server table

Eric-141924

Grasshopper

Points: 17
More actions
May 10, 2004 at 4:53 pm

#102004

Hello all,
A little question regarding SQL Server DB's.
I have a two tables containing customers invoices, one for the invoices header (ie: customer #, invoice date,... KEY: invoice # + invoice date) and another for the details of the invoices (ie: each invoice line details KEY: invoice # + line #). I need to periodically remove invoices older than a certain timeframe (ex: all invoices older than 48 months).
How can I proceed?
I am fairly new with SQL server.... Please help!
Thanks,
Eric

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Indu Jakhar Old Hand Points: 337 More actions · Answer 1

Hi!

You can write the delete statement based on need i.e. where the difference of invoice date and current date is greater than equal to 48 months. This delete statement can be scheduled as a job that can run on the time given for eg. daily/ weekly or whatever frequency you specify. Job can be scheduled using enterprise manager or thru code too.

Regards

Indu

Best Regards,

Indu Jakhar

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

In the interests of referential integrity, you'll probably need two delete statements. The first will delete the relevant invoice lines and the second their corresponding header records.

(Not sure quite why you would delete an invoice, but here goes...)

Your statements will look something like this:

delete il

from

invlines il inner join invhdr ih on il.invno = ih.invno

where ih.invdate < (GetDate() - 90)

delete ih

from

invhdr ih

where ih.invdate < (GetDate() - 90)

You might want to perform the deletes as part of a transaction to ensure that you never find yourself in a situation where there are some invoice headers existing whose lines have been deleted.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Charlie-120533 SSCrazy Points: 2137 More actions · Answer 3

Eric,

I agree with Indu and Phil. I, too, am fairly new to SQL. To be on the safe side, I always use a SELECT statement first to verify that the data I'm deleting is correct. I always test a couple of times before performing any delete on a production server. Also - make a backup of the database prior to deleting.

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 4

Actually I would consider an Archive table

Select ? Into Archive_Inv_DeT WHERE ...

Select ? Into Archive_Inv_Hdr ...

Then Delete

For good luck put a marker in the Archive table indicating the date transfered.

KlK