SQL: Rolling up or archiving transactions past a cutoff date in T-SQL

Another question that I recently saw on Stack Overflow was asking how you SELECT a bunch of transactions back past a cutoff date, add them up, INSERT a roll-up row, and then delete the old ones. The concern was with how you make sure the ones you are deleting are the same rows that you are rolling up (or totalling). Someone else might UPDATE or INSERT a row at the same time.

I see exactly the same issue with people wanting to archive rows off into another table. They SELECT the rows into the other table, then DELETE them. But how do they know they deleted the same rows.

The answer is the OUTPUT clause for the DELETE statement.

Output Clause

When you execute  a DELETE statement (or INSERT, or UPDATE, or MERGE, etc.), you can add an OUTPUT clause that returns details of what was inserted or deleted. The virtual tables are just like you have in triggers.

Example

So let’s start with an example transactions table. I’ve kept it as simple as possible:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Transactions;
GO

CREATE TABLE dbo.Transactions
(
    TransactionID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_dbo_Transactions
        PRIMARY KEY,
    TransactionAmount decimal(18,2) NOT NULL,
    TransactionDate date NOT NULL
);
GO

Then let’s populate it with some sample data and query the data:

SET NOCOUNT ON;

DECLARE @Counter int = 1;
WHILE @Counter <= 50
BEGIN
    INSERT dbo.Transactions 
    (
        TransactionAmount, TransactionDate
    )
    VALUES (ABS(CHECKSUM(NewId())) % 10 + 1, 
                DATEADD(day, 0 - @Counter * 3, GETDATE()));

    SET @Counter += 1;
END;

SELECT * FROM dbo.Transactions;
GO

That leaves us rows like this:

Roll-up Time

Now I’ll calculate an archive cutoff date, as the first day of a month, 2 months back.

DECLARE @CutoffDate date 
  = DATEADD(day, 1, EOMONTH(DATEADD(month, -2, GETDATE())));

DECLARE @TransactionAmounts TABLE
(
    TransactionAmount decimal(18,2)
);

BEGIN TRAN;

DELETE dbo.Transactions WITH (TABLOCK)
OUTPUT deleted.TransactionAmount INTO @TransactionAmounts 
WHERE TransactionDate < @CutoffDate;

IF EXISTS (SELECT 1 FROM @TransactionAmounts)
BEGIN
    INSERT dbo.Transactions (TransactionAmount, TransactionDate)
    SELECT SUM(TransactionAmount), DATEADD(day, 1, @CutoffDate)
    FROM @TransactionAmounts;
END;

COMMIT;

I’ve added a table variable to “catch” the OUTPUT of the DELETE statement. The poster asked for a table lock so I added that, although it’s not really needed or a good idea. (Consider removing it)

Then, within the same transaction, I just INSERT the roll-up row back into the same table.

And Archiving?

Archiving data from one table into another table is even easier. You just do the DELETE in one table, and OUTPUT INTO the other table. Then you know that the rows you deleted are the same ones that you are inserting.

I hope that helps someone.

2019-11-07