Save deleted data through OUTPUT clause with Sql Server 2005Posted: March 24, 2008
You can use OUTPUT clause if you are using Sql Server 2005.
Create a standalone table tblUsersArchive to archive tblUsers with same columns as your original table. (Right click on the table from Management Studio and Script the table as>> Create to>>New Query Edit Window, change the table name and remove the old key and constrains. You can also add a new key here with an identity field. Make sure the archive table does not have triggers or constrains on it. Also no key relationships are defined for all involved columns.
Here is the sample code:
DELETE FROM [tblUsers]
OUTPUT deleted.* INTO tblUsersArchive
WHERE [UserID] = @UserID