I have 24 tables named tblData1 ... tblData24 and I have a scheduled
job that runs successfully to delete all data older than 31 days.
My problem is that I need to keep at least one record in each table
for the aggregate function max() to work in one of my application's
functions, as if there are no records the result is null.
Although I have figured out a workaround in the function using max() I
would like to know how to change my script.
Functionally I would like to get the max() value of the ID column
(autoincrementi ng) and then add to the where "And ID <> @maxID".
I have tried a few options and come unstuck with scope of variables,
and tried to use a temp table to store the max values for the 24
tables and got no where. Can anyone help ?
Working script without the @maxID bit:-
DECLARE @days VARCHAR(12)
DECLARE @intData int
DECLARE @SQL1 VARCHAR(2000)
set @Days = 31
set @intData = 1
While @intData<=24
Begin
SET @SQL1 = 'DELETE FROM [DB1_SQL].[dbo].[tblData'+
rtrim(CONVERT(c har(2), @intData)) + '] Where
datediff(Day,Da tim,getdate())> '+ @days
EXEC(@SQL1)
/*print @SQL1*/
set @intData= @intData + 1
End
go
job that runs successfully to delete all data older than 31 days.
My problem is that I need to keep at least one record in each table
for the aggregate function max() to work in one of my application's
functions, as if there are no records the result is null.
Although I have figured out a workaround in the function using max() I
would like to know how to change my script.
Functionally I would like to get the max() value of the ID column
(autoincrementi ng) and then add to the where "And ID <> @maxID".
I have tried a few options and come unstuck with scope of variables,
and tried to use a temp table to store the max values for the 24
tables and got no where. Can anyone help ?
Working script without the @maxID bit:-
DECLARE @days VARCHAR(12)
DECLARE @intData int
DECLARE @SQL1 VARCHAR(2000)
set @Days = 31
set @intData = 1
While @intData<=24
Begin
SET @SQL1 = 'DELETE FROM [DB1_SQL].[dbo].[tblData'+
rtrim(CONVERT(c har(2), @intData)) + '] Where
datediff(Day,Da tim,getdate())> '+ @days
EXEC(@SQL1)
/*print @SQL1*/
set @intData= @intData + 1
End
go
Comment