The OUTPUT INSERTED clause in SQL Server is a powerful feature that allows you to retrieve the values of the inserted rows during an INSERT operation. This is particularly useful when you want to capture values such as identity column values or other computed values right after they have been inserted into the database.
Syntax
Here is the basic syntax for using the OUTPUT INSERTED clause with an INSERT statement:
INSERT INTO TableName (Column1, Column2, ...)
OUTPUT INSERTED.Column1, INSERTED.Column2, ...
VALUES (Value1, Value2, ...);
Example
Let's consider an example with a table named Employees that has the following structure:
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
JobTitle VARCHAR(50)
);
Now, suppose we want to insert a new employee and capture the inserted EmployeeID along with FirstName and LastName.
INSERT INTO Employees (FirstName, LastName, JobTitle)
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName, INSERTED.LastName
VALUES ('John', 'Doe', 'Software Engineer');
Capturing Data into a Temporary Table
Sometimes you may want to store the output into a temporary table or a table variable for further processing. Here's how you can do that:
-- Creating a temporary table to store the output
CREATE TABLE #InsertedEmployees (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
-- Inserting into Employees and capturing the output
INSERT INTO Employees (FirstName, LastName, JobTitle)
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName, INSERTED.LastName
INTO #InsertedEmployees
VALUES ('Jane', 'Smith', 'Project Manager');
-- Querying the temporary table
SELECT * FROM #InsertedEmployees;
-- Dropping the temporary table
DROP TABLE #InsertedEmployees;
Points to Remember
1. OUTPUT Clause: Can be used with INSERT, UPDATE, or DELETE statements, allowing you to capture affected rows.
2. INSERTED Table: The INSERTED logical table contains the newly inserted rows. For updates, you will also have a DELETED table containing the old values.
3. Multiple Rows: If multiple rows are inserted at once, all will be captured in the result set.
4. Performance: Using OUTPUT can have a performance overhead; ensure it is necessary for your use case.
By utilizing the OUTPUT INSERTED clause, you can easily capture the data of rows being inserted, which can be incredibly useful for logging, auditing, or further processing immediately after the insert operation.