SQL Server Performance Forum – Threads Archive

how to auto clear data during year end

Hi, I need to clear data during year end, how to write a store procedure to auto clear the data during year end? Thank you onn

Schedule a job to delete the data based upon the date as follows: set rowcount 1000
while exists (select 1 from tablename where date < ‘dd/mm/yyyy’)
Begin
Delete from tablename where date < ‘dd/mm/yyyy’
End
set rowcount 0 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

…or did you mean ‘archive data at the end of the year’?. If that is the case, you may want to sue Satya (I think he works for a bank). Nathan H.O.
Moderator
SQL-Server-Performance.com

<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> in this case you can still use the script satya gave you, just add an insert into another table before deleting the rows, and put both operations inside a transaction (inside the loop). and the code inside a store procedure. You could use DateDiff to check the dates.

quote:… to auto clear the data during year end?

To automatically implement any of the above solutions automatically (plus a backup step, just in case) create a job and enable it to run once at the end of operational year (there is no straightforward way of scheduling a yearly job within SQL Server or Windows). You can then change the job date to point to the next year end. My opionion? Run this clearing/archiving stored procedure manually. There is a lot of risk associated with this task to be left to the whims of a computer and its clock. Nathan H.O.
Moderator
SQL-Server-Performance.com

If so Nathan will also get 50% of the result [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />], for giving this idea. My intention is to supply the query and by default the originator should perform all required data backup facility before deleting any production data.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by vbkenya …or did you mean ‘archive data at the end of the year’?. If that is the case, you may want to sue Satya (I think he works for a bank). Nathan H.O. Moderator SQL-Server-Performance.com <hr height="1" noshade id="quote"></blockquote id="quote"> <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

what I meant is to create a job that calls a stored procedure that deletes/moves the rows where datediff(year, date_field, getdate() ) >= 1 then schedule the job as needed.

Satya, you are right. I was just raising a concern over the way the question was stated. I have been asked a similar question before when the intention was totally different. And yes, I have also seen users copy and paste scripts (especially from websites and fora like this one) and run them without modifications or any other careful forethought. At all traffic intersections I always assume that the lights will turn red any moment even if they are not powered and functioning (like where I am currently consulting). It never really hurts to be 110% sure. Nathan H.O.
Moderator
SQL-Server-Performance.com

I agree Nathan and one should always test the phase before implementing on the production, otherwise it will cost the job itself. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Forethought is one of the reasons why Derrick Leggett’s http://www.sql-server-performance.com/forum/pop_profile.asp?mode=display&id=1835] lemons haven’t been falling near my neighbourhood of late. Nathan H.O.
Moderator
SQL-Server-Performance.com

]]>