SQL OUTPUT

What is the SQL OUTPUT clause?

Returns rows affected by INSERT, UPDATE, DELETE, or MERGE statements so you can consume them immediately.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OUTPUT: SQL Server (full support), Azure SQL Database (full support). Other platforms use RETURNING instead (PostgreSQL, Oracle, MySQL 8.0.19+).

SQL OUTPUT Full Explanation

The OUTPUT clause captures the before-and-after column values of rows touched by data-modification statements and streams them to the client or into another table. Introduced in SQL Server 2005, OUTPUT behaves like a temporary result set that exposes the logical deleted and inserted pseudo-tables. It supports INSERT, UPDATE, DELETE, and MERGE, enabling auditing, change capture, chaining modifications, or avoiding a second query to fetch affected rows. When used with INTO, the results are persisted to a target table or table variable; otherwise they are returned to the caller. The clause applies to exactly the rows modified within the statement, is executed atomically with the DML, and respects transaction boundaries. Limitations: cannot reference text/ntext/image columns directly (use INTO with varchar(max) instead), not allowed with triggers that reference the same table, and cannot be combined with the TOP clause without ORDER BY unless INTO is specified.

SQL OUTPUT Syntax

<DML statement>
OUTPUT [INSERTED.|DELETED.]column_name[, ...]
[INTO target_table[(column_list)]]
[VALUES(<expression_list>)]

SQL OUTPUT Parameters

INSERTED.column_name - any column from the rows after modification
DELETED.column_name - any column from the rows before modification
target_table - table or table variable receiving the output (optional)
column_list - list that maps target_table columns to OUTPUT columns (optional)

Example Queries Using SQL OUTPUT

--Return updated rows to caller
UPDATE users
SET last_login = SYSDATETIME()
OUTPUT INSERTED.id, INSERTED.last_login;

--Capture deleted rows into an archive table
DELETE FROM orders
OUTPUT DELETED.*
INTO orders_archive;

--Insert and retrieve new identity values
INSERT INTO customers(name, email)
OUTPUT INSERTED.customer_id
VALUES ('Galaxy', 'info@getgalaxy.io');

--Merge with change-log table
MERGE sales AS target
USING staging_sales AS src ON target.id = src.id
WHEN MATCHED THEN UPDATE SET amount = src.amount
WHEN NOT MATCHED THEN INSERT(id, amount) VALUES(src.id, src.amount)
OUTPUT $action, INSERTED.id, INSERTED.amount
INTO sales_changelog(action, id, amount);

Expected Output Using SQL OUTPUT

Each query returns a result set (or fills the INTO table) containing the specified columns for every row the DML touched
No extra round-trip is required

Use Cases with SQL OUTPUT

Retrieve newly generated identity or GUID values immediately after INSERT
Build change-data-capture or audit tables without triggers
Archive deleted rows during DELETE
Feed data from one table into another within the same transaction
Debug MERGE logic by showing which branch each row followed

Common Mistakes with SQL OUTPUT

Forgetting to qualify columns with INSERTED or DELETED aliases
Using text/ntext/image columns without casting
Expecting OUTPUT to fire inside triggers when the base statement already uses OUTPUT
Combining OUTPUT without INTO alongside TOP without ORDER BY, which causes an error

First Introduced In

SQL Server 2005

Frequently Asked Questions

What columns can I reference in OUTPUT?

You can reference any non-text/ntext/image column from the virtual INSERTED and DELETED tables. Qualify each column with the appropriate alias.

Does OUTPUT run in the same transaction as the DML?

Yes. OUTPUT executes atomically with the data-modification statement. If the transaction rolls back, the output rows are rolled back as well.

How is OUTPUT different from a trigger?

Triggers fire after the DML completes and add overhead on every change. OUTPUT captures changes inline, requires no additional object, and can write to a target table without recursion issues.

Can I use OUTPUT in a stored procedure that already has triggers?

Yes, but you cannot reference OUTPUT on a table if an AFTER trigger on that same table also uses OUTPUT. Remove one of the clauses or refactor your logic.