Using OUTPUT clauses to check data changes in a SQL script

A colleague recently showed me the OUTPUT clause in SQL Server, which I wasn't familiar with. It allows you to view the data which has been changed by a query as part of that query.

For example, to view the data inserted by an INSERT statement, you can write this:

INSERT INTO MyTable 
(
     Name
     ,Description
) 
OUTPUT inserted.* -- put the OUTPUT clause immediately after the INSERT
SELECT
     Name
      ,Description
FROM MyOtherTable 
WHERE Description IS NOT NULL;

As you might imagine, this will output the data which was inserted (as if you had run a SELECT statement to find it).

You can also use the OUTPUT clause with an UPDATE statement:

UPDATE MyTable
SET Description = 'I am testing'
OUTPUT inserted.* -- put the OUTPUT clause after the SET clause
WHERE Name = 'Test';

As shown in the above snippets, SQL Server creates a sort of temporary table called inserted and puts the new data in there. It also creates a second one called deleted which contains any deleted data. This means that for an UPDATE you can select both the old and new values at the same time:

UPDATE MyTable
SET Description = 'I am testing'
OUTPUT 
    deleted.Name
    , deleted.Description AS OriginalDescription
    , inserted.Description AS UpdatedDescription
WHERE Name = 'Test';

If you want to run the above SQL snippets, you can use the below SQL to create the tables you need.

CREATE TABLE MyTable 
(
    Name NVARCHAR(20)
    ,Description NVARCHAR(500)
);
CREATE TABLE MyOtherTable 
(
    Name NVARCHAR(20)
    ,Description NVARCHAR(500)
);

INSERT INTO MyTable
(   
    Name
    ,Description
)
VALUES 
    ('Test', 'Test description');

INSERT INTO MyOtherTable
(   
    Name
    ,Description
)
VALUES 
    ('Foo', 'Foo description')
    ,('Bar', 'Bar description');