Save deleted data through OUTPUT clause with Sql Server 2005

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



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s