I have seen many times people attempting to run 2 queries to determine which records will be deleted, then another to do the deleting. I have some good news for those using SQL 2005 and later. You can now use the OUTPUT clause to combine 2 queries into 1. Here is the example.

Ex. 

USE sqldatabase

Declare @del AS TABLE(delrowID int,delrow nchar(50))

DELETE FROM table

OUTPUT deleted.rowid,deleted.row

INTO @del

WHERE rowid=7

–Display Deleted value

SELECT * FROM @del 
What We have done here is created a temporary table that we will insert into before the Where clause is executed causing the records to be deleted. After we create the temporary table we start our delete statement and then output deleted records into our temporary table as the statment is executed. This will build a recordset in our temp table of the records that have been deleted, allowing you to select those records out for display.
Advertisements