need help coming up with a simple archive script 1

[bugulgad]

Seems simple enough, but I can't figure it out.

SQL 2005
Table A
load_date - pk
company_id - pk

Table B
business_date
month_end_flag

I simply need to write something that will copy over data that is greater than 120 days old...but not any month end data. Once copied to another table....table A can then have these rows deleted.

 

[RiverGuy]

Is this what you need?

BEGIN TRANSACTION;

BEGIN TRY
     INSERT INTO TableA_History 
     SELECT * FROM TableA WHERE DATEDIFF(day, load_date, GETDATE()) >= 120

     DELETE TableA WHERE DATEDIFF(day, load_date, GETDATE()) >= 120

     INSERT INTO TableB_History
     SELECT * FROM TableB WHERE DATEDIFF(day, busindess_date, GETDATE()) >= 120 AND month_end_flag = 0

     DELETE TableB
     WHERE DATEDIFF(day, busindess_date, GETDATE()) >= 120 AND month_end_flag = 0


END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

 

[bugulgad]

thanks. THat was not quite what I was looking for but you pointed me in the right direction and I have figured it out now.

 

[markros]

If there is an index on the Date field, it may be better to write these queries in a sargable form (e.g. use DATEADD function instead of DATEDIFF on the right side of the comparison).