DELETE from two tables with one OUTPUT clause?

Posted by lance on Stack Overflow See other posts from Stack Overflow or by lance
Published on 2010-05-20T15:29:41Z Indexed on 2010/05/20 15:40 UTC
Read the original article Hit count: 331

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?

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about delete