The OUTPUT clause in SQL Server allows you to return information from rows affected by an INSERT, UPDATE, or DELETE statement. This is particularly useful for capturing and working with the altered data, such as logging changes, performing further operations on the affected rows, or retrieving specific columns from those rows.
Using the OUTPUT Clause
1. INSERT Statement
When using the OUTPUT clause with an INSERT statement, you can return the rows that have been inserted.
DECLARE @InsertedRows TABLE (Id INT, Name NVARCHAR(50));
INSERT INTO YourTable (Name)
OUTPUT inserted.Id, inserted.Name INTO @InsertedRows
VALUES ('John Doe'), ('Jane Doe');
SELECT * FROM @InsertedRows;
In this example, the OUTPUT clause captures the Id and Name of the inserted rows and stores them in the temporary table @InsertedRows, which can then be queried afterward.
2. UPDATE Statement
For an UPDATE statement, you can return the old and new values of the columns that are being modified.
DECLARE @UpdatedRows TABLE (Id INT, OldName NVARCHAR(50), NewName NVARCHAR(50));
UPDATE YourTable
SET Name = 'John Smith'
OUTPUT deleted.Name AS OldName, inserted.Name AS NewName INTO @UpdatedRows
WHERE Id = 1;
SELECT * FROM @UpdatedRows;
In this case, the OUTPUT clause captures both the old value (deleted.Name) and the new value (inserted.Name) of the Name column from the row that is being updated.
3. DELETE Statement
When deleting rows, you can also use the OUTPUT clause to return the rows that have been deleted.
DECLARE @DeletedRows TABLE (Id INT, Name NVARCHAR(50));
DELETE FROM YourTable
OUTPUT deleted.Id, deleted.Name INTO @DeletedRows
WHERE Id = 1;
SELECT * FROM @DeletedRows;
Here, the OUTPUT clause captures the Id and Name of the deleted rows and stores them in the @DeletedRows table.
Considerations
- The OUTPUT clause can also be used with more complex queries involving joins or conditions.
- Make sure to declare a table variable (or use a temporary table) to store the output if you need to process it later.
- The OUTPUT clause can be combined with any other clause that you would typically use in the statement.
- You can conditionally limit the output by narrowing down the affected rows with appropriate WHERE clauses.
These examples demonstrate the flexibility and utility of the OUTPUT clause for capturing information about rows affected by data manipulation operations.