When you perform a DML operation using an INSERT, UPDATE, DELETE or MERGE statement, an OUTPUT clause can be used to return information regarding the rows affected. You can find this useful when you need to archive, audit or even troubleshoot the result of DML operations performed on a table.
There are two keywords that can be “activated” when you specify the OUTPUT clause.
1. Inserted:
The inserted keyword is “activated” when you use INSERT with OUTPUT.
For example:
For this example, I will be creating a temporary table called “employee” and I will be inserting some records into the table.
With the OUTPUT clause, we’ll be able to display the records that were inserted into the table.
The inserted keyword is “activated” when you use INSERT with OUTPUT.
For example:
For this example, I will be creating a temporary table called “employee” and I will be inserting some records into the table.
With the OUTPUT clause, we’ll be able to display the records that were inserted into the table.
CREATE TABLE #Employee ( EmpID INT, FirstName VARCHAR(20), LastName VARCHAR(20), BirthDate DATE, HireDate DATE, Salary MONEY, EndDate DATE ) INSERT INTO #Employee ( EmpID, FirstName, LastName, BirthDate, HireDate, Salary, EndDate ) OUTPUT inserted.EmpID, inserted.FirstName, inserted.LastName, inserted.Salary, inserted.EndDate VALUES (1, 'John', 'Brown', '01-01-1960', '10-01-1985', 85000.00, NULL), (2, 'Pete', 'Dawn', '12-03-1965', '12-01-2000', 60000.00, NULL), (3, 'Amanda', 'Lopez', '06-01-1980', '12-01-2005', 45000.00, NULL), (4, 'Adam', 'Smith', '02-26-1971', '04-23-2001', 70000.00, NULL), (5, 'Taylor', 'Ramsey', '03-01-1984', '11-11-2004', 60000.00, NULL)
Resultset:
The OUTPUT clause in this case basically worked as a SELECT statement, giving us information about the data that was inserted into the table.
2. Deleted:
When the OUTPUT clause is used in conjunction with a DELETE statement, the deleted keyword is enabled.
For example:
Based on the sample we created above, I am going to delete records belonging to employee ID 5.
DELETE FROM #Employee OUTPUT deleted.EmpID, deleted.FirstName, deleted.LastName, deleted.Salary, deleted.EndDate WHERE EmpID = 5
You can see that the resultset only shows us the data that was deleted and nothing more.
3. Inserted and Deleted:
Both the inserted and deleted keywords are enabled when the OUTPUT clause is used alongside an UPDATE statement.
For example:
I am going to update the salary of Pete who has an Employee ID “2” from $60,000 to $65,000. Here we will see how the OUTPUT clause behaves with an UPDATE statement.
For example:
I am going to update the salary of Pete who has an Employee ID “2” from $60,000 to $65,000. Here we will see how the OUTPUT clause behaves with an UPDATE statement.
UPDATE #Employee SET Salary = 65000 OUTPUT inserted.EmpID, inserted.FirstName, inserted.LastName, inserted.Salary, inserted.EndDate, deleted.EmpID, deleted.FirstName, deleted.LastName, deleted.EndDate WHERE EmpID = 2
From the resultset above, you can see that I was able to utilize both the inserted keyword and the deleted keyword. This is because an UPDATE is simply SQL Server deleting an old record and inserting a new record in its place
CONCLUSION
There are so many uses to the OUTPUT clause such as for archiving, auditing and informational purposes. You can even input the result from the OUTPUT into a table or even use it in a stored procedure.
There are so many uses to the OUTPUT clause such as for archiving, auditing and informational purposes. You can even input the result from the OUTPUT into a table or even use it in a stored procedure.
