Insert into from deleted output clause

Question

I am archiving my table data to archive tables. For this I am just inserting the records to the archive table based on some condition and in the next statement I delete the records with the same condition in the main table.

But to improve performance it was suggested to use a single statement with an output clause.

Code :

INSERT INTO AR_tbl1 
SELECT GETDATE(), D.*
FROM
    (DELETE FROM tbl1 
    WHERE Amt >= 40  
    OUTPUT DELETED.*) D

But this is not working. If I comment the where clause it works. Please help me to fix the logic with where clause

Answer 1

Although this question has an accepted answer, I think the following query would be more concise instead of using derived table.

DELETE FROM tbl1 
OUTPUT GETDATE(), DELETED.*
INTO AR_tbl1
WHERE Amt >= 40