The OUTPUT clause in SQL Server is a powerful feature that allows you to return data from the rows affected by INSERT, UPDATE, or DELETE operations. This can be especially useful for logging, auditing, or returning values back to the application without having to perform additional SELECT statements.
Syntax Overview
The basic syntax for using the OUTPUT clause is as follows:
INSERT INTO target_table (column1, column2, ...)
OUTPUT inserted.column1, inserted.column2, ...
VALUES (value1, value2, ...);
UPDATE target_table
SET column1 = value1, column2 = value2, ...
OUTPUT updated.column1, updated.column2, ...
WHERE some_condition;
DELETE FROM target_table
OUTPUT deleted.column1, deleted.column2, ...
WHERE some_condition;
Example Usage
1. Inserting Data with OUTPUT
When inserting data, you might want to capture the new values being added:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
INSERT INTO Employees (EmployeeID, FirstName, LastName)
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName
VALUES (1, 'John', 'Doe'), (2, 'Jane', 'Smith');
This will insert two records into the Employees table and return the EmployeeID, FirstName, and LastName of the newly inserted records.
2. Updating Data with OUTPUT
When updating data, you might want to capture the old and new values:
UPDATE Employees
SET LastName = 'Johnson'
OUTPUT deleted.LastName AS OldLastName, inserted.LastName AS NewLastName
WHERE EmployeeID = 1;
This command will update the LastName of the employee with EmployeeID = 1 to 'Johnson' and return both the old and new last names.
3. Deleting Data with OUTPUT
When deleting data, you want to capture the data being removed:
DELETE FROM Employees
OUTPUT deleted.EmployeeID, deleted.FirstName, deleted.LastName
WHERE EmployeeID = 2;
This will delete the employee with EmployeeID = 2 and return the ID, first name, and last name of the deleted employee.
Storing the OUTPUT in a Table
You can also store the results of the OUTPUT clause into a table:
CREATE TABLE DeletedEmployees (
EmployeeID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
DELETE FROM Employees
OUTPUT deleted.EmployeeID, deleted.FirstName, deleted.LastName INTO DeletedEmployees
WHERE some_condition;
In this case, the deleted rows are captured and stored in the DeletedEmployees table.
Conclusion
The OUTPUT clause is a very flexible feature in SQL Server that enhances data manipulation statements by allowing the immediate capture of affected rows without needing additional queries. This is particularly helpful for tracking changes or logging modifications to data within your application.