CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Akhil
Akhil

Reputation: 2030

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

Upvotes: 7

Views: 12932

Answers (2)

Adnan Sharif
Adnan Sharif

Reputation: 967

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

Upvotes: 12

Akhil
Akhil

Reputation: 2030

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

Upvotes: 9

Related Questions