Post reply

Data truncation/deletion

  • November 10, 2010 at 3:14 pm

    #147555

    I need to on a daily basis delete records older than 90 days. I am wondering how to best accomplish this as an auto process each day?

    Thanks for any thoughts on this...

  • November 10, 2010 at 3:15 pm

    #1249197

    I assume the table has a timestamp of some kind on it?

    If so, create a SQL Agent job with a T-SQL statement in it that DELETE FROM table WHERE timestampCol <= DATEADD( dd, -90, GETDATE())

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 10, 2010 at 3:51 pm

    #1249201

    Yes... it does have one...

    Thanks for the help

  • November 11, 2010 at 6:31 am

    #1249456

    If you want to archive the deleted records, use:

    DELETE FROM table

    OUTPUT deleted.* INTO archive_table

    WHERE timestampCol <= DATEADD( dd, -90, GETDATE())

  • November 11, 2010 at 10:09 am

    #1249590

    Great idea... thanks!

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

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