Hello,
I have a table (DataLog) that take log data from a web application.
Data is simply: IdLog - Message - Date
I need to clean this table every week (for example) and save old log entries for every week and save them in other table like:
DataLog_2019_34
DataLog_2019_33
DataLog_2019_32
where 32,33,34 is the week of the year (any other ideas are well accepted).
Is it possible to accomplish it with a trigger?
Thanks a lot in advance.
Luigi
How are these log tables being created?
For example:
IdDataLog int identity(1,1) primary Key,
Message varchar(2000),
LogDate datetime
Luigi
Who creates these log tables? Are they created manually or automated fashion
It was already manual created by past developer. I've found It already in place.
You could do it with a trigger or with a SQL Job scheduled to run at an interval (hourly/every 15min). With a trigger it would write out to your archive tables instantly.
Also is there any reason you are creating a new table for each week?
No problem for creating a table (real) every week. The space is in large amount. But, anyway, how can I write a trigger like this?
Another idea
is create a stored proc
create a job ...in that job call stored proc
and schedule the job to run every week
please see link below
hope this helps 
You wouldn't use a trigger for that. You'd create separate code, such as a stored procedure, that would to the copying/archiving.
Most importantly, the table should not be keyed like this:
Instead, it should be like this:
IdDataLog int IDENTITY(1, 1) NOT NULL,
Message varchar(2000) NOT NULL,
LogDate Datetime NOT NULL,
PRIMARY KEY ( LogDate, IdDataLog )
That will cluster the rows in the LogDate order first, making copying and lookups vastly more efficient.
Thank you Harish.
Thank you Scott.