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

```sql
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:

```sql
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:

```sql
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.

```sql
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');
```