Insert into from deleted output clause

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

I finished. thanks Martin. The problem lies in the order. Output clause needs to add before the where clause.

New Code :

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

Advertisement

Answer