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>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