Have you ever wondered how to retrieve the affected rows when performing a DELETE, INSERT, or UPDATE operation in SQL Server? The OUTPUT clause is here to help! In this blog post, we will explore the power of the OUTPUT clause and how it can be used to display the exact affected rows.

Delete Actions with OUTPUT

Let’s start with a simple example. Imagine you have a table called “Location” that contains various locations of a company. You want to delete certain rows from this table, but you also want to know which rows were affected. The DELETE statement alone doesn’t provide this information, but the OUTPUT clause does.

By using the OUTPUT clause, you can display the exact affected rows, even though they are no longer present in the table. This can be useful when you need to track the changes made to your data. For example, if you run a DELETE query to remove certain locations from the “Location” table, the OUTPUT clause can show you the deleted rows.

Insert Actions with OUTPUT

The OUTPUT clause is not limited to DELETE operations. It can also be used with INSERT statements. When you insert records into a table, the OUTPUT clause can create a temporary table called “Inserted” to show you the inserted rows. This can be helpful when you want to verify the data that was just inserted.

Update Actions with OUTPUT

Similar to DELETE and INSERT operations, the OUTPUT clause can also be used with UPDATE statements. When you update records in a table, the OUTPUT clause can create both an “Inserted” and a “Deleted” table. The “Inserted” table contains the updated rows, while the “Deleted” table contains the old rows before the update.

This is particularly useful when you need to track the changes made with an UPDATE statement. By using the OUTPUT clause, you can see both the old and new records side by side. This can help you analyze the changes made to your data.

Quiz Time!

Now it’s time for a quiz! Imagine you have an “HourlyPay” table and you want to give all hourly employees a $1 raise. You want to see the EmpID, OldPay, and NewPay for each updated record. Which code snippet will achieve this result?

1. UPDATE HourlyPay SET Hourly = Hourly + 1 OUTPUT Deleted.EmpID, Deleted.Hourly as OldPay, Updated.Hourly as NewPay WHERE Hourly IS NOT NULL
2. UPDATE HourlyPay SET Hourly = Hourly + 1 OUTPUT Deleted.EmpID, Updated.Hourly as OldPay, Deleted.Hourly as NewPay WHERE Hourly IS NOT NULL
3. UPDATE HourlyPay SET Hourly = Hourly + 1 OUTPUT Deleted.EmpID, Inserted.Hourly as OldPay, Deleted.Hourly as NewPay WHERE Hourly IS NOT NULL
4. UPDATE HourlyPay SET Hourly = Hourly + 1 OUTPUT Deleted.EmpID, Deleted.Hourly as OldPay, Inserted.Hourly as NewPay WHERE Hourly IS NOT NULL

Please leave your answer in the comment section below along with your country of residence. Every day, one winner will be announced from the United States and one winner from India. The winners will receive a copy of “Joes 2 Pros Volume 2”. The contest is open until the next blog post is published.

Thank you for reading this blog post on the OUTPUT clause in SQL Server. We hope you found it informative and helpful. Stay tuned for more SQL Server tips and tricks!