ArchiveSimAppTransaction

CREATE PROCEDURE [dbo].[ArchiveSimAppTransaction]

@rowsToArchivePerBatch BIGINT,
@batchCount INT

AS


SET NOCOUNT ON;

DECLARE @start BIGINT
DECLARE @end BIGINT
DECLARE @maxSimAppTransIDLimit BIGINT
DECLARE @count BIGINT
DECLARE @daystoDeleteFrom SMALLINT
DECLARE @today DATETIME

SET @count = 1
SET @daystoDeleteFrom = -7
SET @today = GETDATE()

--get the highest simappgenid from x days ago directly from MMFSIMTransactions using our artificial index.
SELECT @maxSimAppTransIDLimit = SimAppTransID FROM dbo.LookupMaxIDs (NOLOCK) WHERE
DATEDIFF(d, @today, Date) = @daystoDeleteFrom

WHILE @batchCount > @count
BEGIN

BEGIN TRANSACTION

SELECT @start= MIN(SimAppTransID) FROM MMFSIMTransactions.dbo.SimAppTransaction (NOLOCK)
SELECT @end = @start + @rowsToArchiveperBatch

INSERT INTO MMFSIMTranHistory.dbo.SimAppTransaction
(SimAppTransID,TransactionTypeID, CreateTime)
SELECT SimAppTransID,TransactionTypeID, CreateTime
FROM MMFSIMTransactions.dbo.SimAppTransaction WITH (NOLOCK)
WHERE (SimAppTransaction.SimAppTransID BETWEEN @start AND @end)
AND SimAppTransaction.SimAppTransID < @maxSimAppTransIDLimit


DELETE FROM MMFSIMTransactions.dbo.SimAppTransaction
WHERE (SimAppTransaction.SimAppTransID BETWEEN @start AND @end)
AND SimAppTransaction.SimAppTransID < @maxSimAppTransIDLimit

SET @count = @count + 1

IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN
END

WAITFOR DELAY '00:00:05'
END



--EXEC ArchiveSimAppTransaction 10000, 2