In SQL Server, the OUTPUT clause can be quite useful when you want to see the results of an UPDATE statement immediately after the update has been executed. This allows you to retrieve the rows that were affected, along with their new values after the update.
Here’s a basic example of how to use the OUTPUT clause in an UPDATE statement:
Syntax
UPDATE target_table
SET column1 = new_value1,
column2 = new_value2
OUTPUT inserted.*
WHERE condition;
Example
Assume we have a table named Employees with the following structure:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2)
);
Let's say we want to update the Salary of some employees and want to see the updated rows:
UPDATE Employees
SET Salary = Salary * 1.10 -- giving a 10% raise
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.Salary
WHERE Salary < 50000; -- condition to find employees whose salary is less than $50,000
Explanation
1. SET Clause: This part updates the Salary of employees by increasing it by 10%.
2. OUTPUT Clause: Here, inserted refers to the rows that have been updated. By specifying inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.Salary, we are selecting the specific columns we want to return for the rows that were updated.
3. WHERE Clause: This filters the rows to be updated, such that only the employees with a salary below $50,000 are considered.
Notes
- The inserted keyword is a special table that holds the new values of the rows affected by the UPDATE statement.
- You can also use deleted to access the original values of the rows before the update, if needed.
- The OUTPUT clause can be directed to an OUTPUT table variable or a temporary table if you want to store the output for further processing.
Example of Storing the Output
DECLARE @UpdatedEmployees TABLE (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2)
);
UPDATE Employees
SET Salary = Salary * 1.10
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.Salary INTO @UpdatedEmployees
WHERE Salary < 50000;
SELECT * FROM @UpdatedEmployees;
In this case, we declare a table variable @UpdatedEmployees to store the output of the updated rows for later use. After the update, we can select from this variable to view the updated information.