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?