How to find row values before and update or delete query in SQL Server

Question

I am really wondering, whether below given query is what best method to get the row values before and after we update or delete query. If some one knows better way getting this result, pls share it with me.

Below is the way I find the values
Consider I have a table called product with productid, price, count

ProductId , Price , ItemCount
(1 , 2.00 , 40)
(2 , 10.00 , 20)
(3 , 5.0 , 28)
(4 , 8, 25)

Now consider, I have received 10 more items on each product which has the price <= 5, so I update my table with the new value. But to keep track what was the count before update and what is the count after update I use below query.

SELECT ProductId, ItemCount INTO #BeforeUpdate FROM Product WHERE Price <= 5
UPDATE Product SET ItemCount = ItemCount + 10 WHERE Price <= 5
SELECT ProductId, ItemCount INTO #AfterUpdate FROM Product WHERE Product ID IN (SELECT ProductId FROM #BeforeUpdate)

Now I can compare both the temporary table and figure out what happened.

Answer 1

I think an easy way to do this functionality is using Output Clause. This Output clause can be used in Insert, Update and Delete statement. During this operation it will give us 2 pseudo tables called "inserted" and "deleted" tables. "Inserted" table will have the information after the query executed and "deleted" table will have the information before the query gets executes. So, you can achieve the same functionality using the below code.

UPDATE Product 
SET ItemCount = ItemCount + 10 
OUTPUT inserted.ProductId, deleted.ItemCount as [old count], inserted.ItemCount as [New count]
WHERE Price <= 5

Your output would be
ProductId , Old count, New count
(1 , 40 , 50)
(3 , 28 , 38)

If you want to use it inside your stored procedure and use the result for some other process in the same stored procedure then you may want to create some table variable at use the result for further process

declare @compare table
(
 ProductId int not null,
 [Old count] int,
 [New count] int
)

UPDATE Product 
SET ItemCount = ItemCount + 10 
OUTPUT inserted.ProductId, deleted.ItemCount as [old count], inserted.ItemCount as [New count] INTO @compare
WHERE Price <= 5

SELECT * FROM @compare

Your output would be same as previous
ProductId , Old count, New count
(1 , 40 , 50)
(3 , 28 , 38)

Note: OUTPUT clause must be used after last SET and before WHERE Condition