DELETE from two tables with one OUTPUT clause?

Question

This deletes the document from the Document table and outputs information about the deleted document into the FinishedDocument table.

DELETE
FROM Document
OUTPUT Deleted.DocumentId
    , Deleted.DocumentDescription
INTO FinishedDocument
WHERE DocumentId = @DocumentId

I need to delete the document not just from the Document table, but also from the DocumentBackup table. Meanwhile, I need to maintain insertion into FinishedDocument.

Is all of this possible with only one statement? If not, is a second DELETE (against DocumentBackup), with all of it wrapped in a transaction, the way to go?

Answer 1

You'll need two DELETEs, but you should use two OUTPUTS on the first DELETE to do both the insert into FinishedDocument and into a table variable to save all of the deleted documents.

try:

DECLARE @DeletedDocuments table (DocumentId int)

BEGIN TRANSACTION

DELETE
    FROM Document
    OUTPUT Deleted.DocumentId, Deleted.DocumentDescription
        INTO FinishedDocument
    OUTPUT Deleted.DocumentId
        INTO @DeletedDocuments
WHERE DocumentId = @DocumentId

DELETE
    FROM DocumentBackup
    WHERE EXISTS (SELECT 1 FROM @DeletedDocuments d WHERE DocumentBackup.DocumentId=d.DocumentId)


COMMIT