Help. Automated purge of tables by date/name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help. Automated purge of tables by date/name

Author

Topic

smithy
Starting Member

2 Posts

Posted - 2012-07-09 : 21:28:16

Im trying to figure out a way to purge data from tables in an automated way instead of having to run the stored proc manually. Basically anything not including the current month and the 2 months prior.
..
declare curs cursor for (
select name from sysobjects
where name like '%ACC_DT1_DBF%' and (name not like'%FEB2012%' and name not like'%JAN2012%' and name not like '%DEC2011%'))
..

Table Created Date
ACCOUNT#DEC2011#ACC_DT1_DBF 01/12/2011 5:51:47.566 AM
ACCOUNT#JAN2012#ACC_DT1_DBF 31/12/2011 5:57:21.260 AM
ACCOUNT#FEB2012#ACC_DT1_DBF 01/02/2012 6:01:41.253 AM
ACCOUNT#MAR2012#ACC_DT1_DBF 01/03/2012 5:58:18.046 AM
ACCOUNT#APR2012#ACC_DT1_DBF 31/03/2012 4:51:59.193 AM
ACCOUNT#MAY2012#ACC_DT1_DBF 01/05/2012 4:22:08.370 AM
ACCOUNT#JUN2012#ACC_DT1_DBF 01/06/2012 4:10:04.246 AM
ACCOUNT#JUL2012#ACC_DT1_DBF 30/06/2012 4:51:16.870 AM

For example I ran the above in February and excluded Feb(current) Jan and Dec (2months)

I tried using current date vs current month and subtracting but run into the issue of if its month 1 then it will look for 0 and -1. Any ideas??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 21:31:13

[code]
delete from table
where createddate < dateadd(mm,datediff(mm,0,getdate())-2,0)
and .... (other condition)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-09 : 21:32:33

quote:
I tried using current date vs current month and subtracting but run into the issue of if its month 1 then it will look for 0 and -1. Any ideas??


use DATEADD(month, -1, <date> )

KH
[spoiler]Time is always against us[/spoiler]

smithy
Starting Member

2 Posts

Posted - 2012-07-09 : 22:09:23

Thanks guys worked perfectly :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 23:12:44

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources