June 23, 2005 at 1:50 am
Hi!
I have to delete all the records from a table after six months. I want to know if I can find a script or maybe you can can help me with another idea. I know I have to use a trigger but i don't know how to write the script.
Thanks!
June 23, 2005 at 4:45 am
You just need to create one script and you schedule in your sql server job, but in your table must have a column that have a default value, when the record inserted.
June 23, 2005 at 5:37 am
Trigger would probably NOT be the way to go.
How 'bout DELETE FROM dbo.TableX WHERE SomeDateTime <= DATEADD(MONTH, -6, GETDATE()) put that in a job and run it???
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 23, 2005 at 5:40 am
http://www.sqlservercentral.com/scripts/contributions/1281.asp will delete all the records in a the DEFAULT database
My Blog:
June 24, 2005 at 6:58 am
dinesh that delete script using sp_msforewachtable does not take into consideration foreign key hierarcies....so it will by default try to delete the4 tables in the order they were created...most likely a FK would interrupt the chain of events, and not all tables would be truely empty.
i would suggest using this script instead, and rather than select the required statement, turn it into dynamic SQL in a cursor to execute each statement:
set nocount on
declare @level tinyint
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level tinyint not null)
insert into #tables (id, TableName, Level)
select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0
from sysobjects where xtype = 'U' and status > 0
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName from #tables where level = 0
select 'DELETE ' + TableName from #tables where level > 0 order by level
drop table #tables
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply