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:

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

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.

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.

 

Leave a Reply

Your email address will not be published.