How to delete last 6 months data using storedprocedure where table does'nt contains timestamp field

_{Thursday, June 26, 2008 12:05 AM}

Hi,

Can anybody help me how to delete last 1 year data from a sql server table using StoredProcedure where

I have two tables table1 and table2 , table1 contains KeyId,PurchaseDate,OrderNumber columns and table2 contains KeyId,PurchaseNumber,OrderStatus columns,In these two tables KeyId is common.So can anybody help me how to delete last 6 months data using storedprocedure that should delete data in two tables at a time.

Please send me reply ASAP.

Thanks in Advance

RJ

_{Thursday, June 26, 2008 1:18 AM}

Suppose you will define date through PurchaseDate in T1 and link T1 and T2 with KeyId.

DELETE FROM T2

FROM T1

INNER JOIN T2

ON T1.KeyId = T2.KeyId

WHERE T1.PurchaseDate < DATEADD(YEAR, -1, GETDATE())

GO

DELETE FROM T1 WHERE T1.PurchaseDate < DATEADD(YEAR, -1, GETDATE())

GO

_{Thursday, June 26, 2008 12:28 PM}

Thanks for the reply,

But Im little bit confused with this Stored procedure,Can you Please send me

clear SP

Thanks in Advance.

RJ

_{Thursday, June 26, 2008 1:04 PM}

Please clarify what delete last year of data means.  Does this mean delete data that is older than 1 year from today or does this mean delete all data that happened last year.  The difference is huge.  The first mentioned scenario will delete records older than 6/26/2007, while the second scenario will delete all from 2007.  Also why does your subject say 6 months of data???

You dont need to delete from both tables at the same time; you need to delete from table two and then from table 1.  If a lot of data is being deleted, I would take an approach that does batch deletes. 

I have not checked this for correctness, so let me know if there is a problem.  I would also suggest you test this guy out before deleteing your data.  You can change the delete to selects to test what is being deleted.

Code Snippet

CREATE PROCEDURE DeleteAgedData_sp

@BatchSize INT = 1000,

@Criteria INT = -1

AS

BEGIN

--=======================================================================

-- DELETE FROM TABLE 2

--=======================================================================

WHILE EXISTS(SELECT 1

   FROM T1

INNER JOIN T2

ON T1.KeyId = T2.KeyId

   WHERE T1.PurchaseDate < DATEADD(YEAR, @Criteria, GETDATE()))

BEGIN

DELETE TOP (@BatchSize)

FROM T2

WHERE EXISTS(SELECT 1

   FROM T1

   WHERE T1.KeyId = T2.KeyId AND

    T1.PurchaseDate < DATEADD(YEAR, @Criteria, GETDATE()))

 

--perform some other operations or wait

WAITFOR DELAY '00:0:30';--HH:MM:SS

END;

 

--=======================================================================

-- DELETE FROM TABLE 1

--=======================================================================

WHILE EXISTS(SELECT 1

   FROM T1

   WHERE T1.PurchaseDate < @Criteria)

BEGIN

DELETE TOP (@BatchSize)

FROM T1

WHERE T1.PurchaseDate < DATEADD(YEAR, @Criteria, GETDATE())

 

--perform some other operations or wait

WAITFOR DELAY '00:0:30';--HH:MM:SS

END;

END

 

 

---

_{Thursday, June 26, 2008 3:08 PM}

Thanks for the reply

Need to delete data which is 6 month old from today and  I need to schedule this deletion job in Sql server agent..so please suggest me with solution ASAP

 

Thanks in Advance,

RJ

---

_{Thursday, June 26, 2008 3:13 PM}

Create a SSIS Package and schedule that in sql job server, that will take care of the deletion.

 

YOu can also schedule SSIS package using COntrol M

 

please let me know if you need more information.

 

Ravi

 

---

_{Thursday, June 26, 2008 3:50 PM}

Okay, then change the date code I posted to (shown below) and pass in the parameter -6.  If you do not need batch processing you can simply execute the delete statements.

 

Code Snippet

DATEADD(month, @Criteria, GETDATE()))

 

---

_{Friday, June 27, 2008 4:19 AM}

Adam,
Can you tell me the purpose of using below statement in the SP

@BatchSize INT = 1000,

And is this neccessary to use while scheduling thru SQL Server Agent?Please let me know Im new to this technology

Thanks in Advance,
RJ

---

_{Friday, June 27, 2008 4:50 AM}

The reason I put this parameter in the sp is to allow you to delete records in batch.  Meaning instead of deleting 10000 rows at one time.  Passing in the value 1000 would delete 1000 records at a time.  This reduces disk contention and locking.  This also gives you a breather to perform other tasks, such as backing up the tran log because it gets huge.

 

You can remove this parameter and the batch statements if you dont need them.  I put it there in case you do.

---

_{Friday, June 27, 2008 1:07 PM}

Adam,

If i remove batchsize then what i need to put in delete statment to delete?

DELETE TOP (@BatchSize)

Please help me at this point

 

Thanks in Advance

RJ

---

_{Friday, June 27, 2008 5:28 PM}

Just remove everthing but the delete statements.

 

Code Snippet

CREATE PROCEDURE DeleteAgedData_sp

@Criteria INT = -6

AS

BEGIN

BEGIN TRY

BEGIN TRANSACTION

 

DELETE

FROM T2

WHERE EXISTS(SELECT 1

   FROM T1

   WHERE T1.KeyId = T2.KeyId AND

    T1.PurchaseDate < DATEADD(month, @Criteria, GETDATE()));

 

DELETE

FROM T1

WHERE T1.PurchaseDate < DATEADD(YEAR, @Criteria, GETDATE());

 

COMMIT TRANSACTION

END TRY

 

BEGIN CATCH

 

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

 

ROLLBACK TRANSACTION

END CATCH

END

 

 

The executed like this EXEC DeleteAgedData_sp --a parameter is optional as the default is -6 (older than 6 months)

 

I hope this is clearer for you b/c I do not know how to make it any more clear.

 

-Adam

 

---

_{Wednesday, July 2, 2008 4:26 AM}

Adam,

When I execute this SP its showing  zero rows effected , its not deleting the rows,Please verify at your end and send me the correct SP  ASAP.

 

Thanks in Advance,

RJ

---

_{Wednesday, July 2, 2008 12:44 PM}

Well this is partly my fault.  When I created the post I forgot to change out one piece of code.  in the second delete the date function is deleting everything that is older than 6 years. 

 

Change this

Code Snippet

DATEADD(YEAR, @Criteria, GETDATE());

 

To

Code Snippet

DATEADD(MONTH, @Criteria, GETDATE());