Archiving with the OUTPUT clause

Most database developers have been faced with the task to archive old data. It could look something like this:

CREATE TABLE dbo.Cars(
  CarID int identity(1,1) PRIMARY KEY,
  BrandName varchar(100),
  ModelName varchar(100),
  releaseYear smallint
);

CREATE TABLE dbo.Cars_Archive(
  CarID int,
  BrandName varchar(100),
  ModelName varchar(100),
  releaseYear smallint,
  ArchivedDateTime datetime DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_Cars_Archive PRIMARY KEY(CarID, ArchivedDateTime)
)

And updating a row would often require a stored procedure and some explicit transactions

--Note that this is very simplified.
CREATE PROC dbo.UpdateCars(@CarID int, @Brandname varchar(100), @ModelName varchar(100), @releaseYear smallint)
AS
BEGIN TRY
  BEGIN TRAN
  INSERT dbo.Cars_Archive(CarID, BrandName, ModelName, releaseYear)
    SELECT CarID, BrandName, ModelName, releaseYear
    FROM dbo.Cars
    WHERE CarID = @CarID;

  UPDATE dbo.Cars
    SET Brandname = @brandName,
        ModelName = @ModelName,
        releaseYear = @releaseYear
        WHERE CarID = @CarID;
  COMMIT
END TRY
BEGIN CATCH
  --You would of course do more than just Rolling back the transaction here, but this works as an example
  IF @@TRANCOUNT&amp;amp;amp;amp;amp;amp;amp;amp;gt;0
    ROLLBACK;
END CATCH

This works. But complexity always grows quite a bit when you have to use explicit transactions. And if you (like me) are lazy, you want to write as little code as possible.
And this is where the OUTPUT-clause comes into play.
The OUTPUT-clause can be added to UPDATE, INSERT, DELETE and MERGE and will provide you with a way to view how rows looks before and after a change.
I will provide an example of how the above UPDATE-procedure could be rewritten to a single SQL-statement, thus getting rid of the explicit transaction handling (a single statement is atomic and will either commit or fail, all by itself).


INSERT dbo.Cars_Archive(CarID, BrandName, ModelName, releaseYear)
SELECT CarID, BrandName, ModelName, releaseYear FROM
(
UPDATE dbo.ActiveCars SET BrandName='SAAB'
OUTPUT deleted.CarID, deleted.BrandName, deleted.ModelName, deleted.releaseYear
WHERE CarID = 2
) as t;

The above code will both update the Cars table and archive the old row from the Cars table into the Cars_Archive table.

The syntax of the OUTPUT-clause isn’t really that complicated – it reminds a lot about what you can do inside a trigger. You have an inserted- and a deleted-table, just like in a trigger.

Read more about the OUTPUT-clause here: https://msdn.microsoft.com/en-us/library/ms177564(v=sql.105).aspx

Leave a Reply Cancel reply