Any time you prepare to delete data from a database, you will probably wonder whether or not to log the deleted data somewhere… just in case.

One way to do this is to construct a SELECT statement to identify the rows you want to delete, and then use it to copy those rows into a separate log table, before doing the actual delete. If you’re using SQL Server, another option is to use the OUTPUT clause. This article discusses the later.

The OUTPUT clause allows us to capture the data affected by INSERT, UPDATE, DELETE, and MERGE operations.

We can use this clause either to return the affected rows to the user, save them to a table variable, or log them into another table.

Let’s go ahead and run some examples that demonstrate how we can use the OUTPUT clause to view and log deleted rows in SQL Server.

Setup: Create the Table and Insert Data

First, let’s create a table and insert some data to delete:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (1, 101, '2024-01-15'),
       (2, 102, '2024-02-10'),
       (3, 103, '2024-03-05'),
       (4, 104, '2024-03-05'),
       (5, 105, '2024-03-05');

Now let’s go through and delete some of those records.

Example 1: Return Deleted Rows to the User

This example shows how to use the OUTPUT clause to return the deleted rows directly to the user:

DELETE FROM Orders
OUTPUT 
    DELETED.OrderID, 
    DELETED.CustomerID
WHERE OrderID = 2;

Output:

OrderID  CustomerID
-------  ----------
2        102       

This query deleted the row where OrderID = 2 and returned the deleted OrderID and CustomerID.

Here, I decided not to return all columns – I omitted the OrderDate column. We can choose whichever columns we want, as long as we prefix it with DELETED.

The DELETED prefix specifies the value deleted by the update or delete operation, and any existing values that don’t change with the current operation. It reflects the value before the DELETE operation is completed.

Example 2: Save the Result to a Variable

We can save the deleted rows to a table variable for further processing if required. To do this, we need to use the INTO clause of the OUTPUT clause:

-- Create a table variable to store output
DECLARE @DeletedOrders TABLE (
    OrderID INT, 
    CustomerID INT, 
    OrderDate DATE
    );

-- Delete data and capture the deleted values
DELETE FROM Orders
OUTPUT 
    DELETED.OrderID, 
    DELETED.CustomerID, 
    DELETED.OrderDate
    INTO @DeletedOrders
WHERE OrderID = 3;

-- Display the deleted rows
SELECT * FROM @DeletedOrders;

Output:

OrderID  CustomerID  OrderDate               
-------  ----------  ------------------------
3        103         2024-03-05T00:00:00.000Z

I returned the result in a SELECT statement, but we could also use the variable to do whatever we want.

In this case I decided to include the OrderDate column.

Example 3: Log Deleted Rows into a Table

One obvious use case for the deleted data is to log it to a permanent (or temporary) table. We can use the OUTPUT INTO clause to do this:

Setup: Creating the Log Table

Let’s create a log table to store the deleted rows:

CREATE TABLE DeletedOrdersLog (
    OrderID INT,
    CustomerID INT,
    DeletedOn DATETIME DEFAULT GETDATE()
);

DELETE with OUTPUT INTO Clause

Now we can go ahead with deleting stuff and logging it in our log table:

-- Delete the row/s
DELETE FROM Orders
OUTPUT 
    DELETED.OrderID, 
    DELETED.CustomerID, 
    GETDATE() 
    INTO DeletedOrdersLog (
        OrderID, 
        CustomerID, 
        DeletedOn
        )
WHERE OrderID = 4;

-- Return the deleted row/s from the log table
SELECT * FROM DeletedOrdersLog;

Output:

OrderID  CustomerID  DeletedOn               
-------  ----------  ------------------------
4        104         2024-09-20T01:58:18.460Z

This query deletes the row where OrderID = 4 and logs the OrderID, CustomerID, and the current timestamp into the DeletedOrdersLog table.