Something that is a common problem in mssql server is deleting a very large number of rows from a table. Normally due to locking the table that the very long delete is running on. There is a few common ways of doing this. Mostly by setting row count and looping though a delete until it runs out of rows to delete. In sql server 2008 it is also possible todo this by moving the data from one table to another using a delete and the output clause.

SET ROWCOUNT 5000
WHILE (1 = 1)
BEGIN
        BEGIN TRAN
        DELETE FROM T WHERE N > 5000
        IF @@ROWCOUNT = 0
                BREAK
END
SET ROWCOUNT 0

Or like this

SET ROWCOUNT 5000
WHILE EXISTS(SELECT * FROM T WHERE N > 5000)
BEGIN
        DELETE FROM T WHERE N > 5000
END
SET ROWCOUNT 0

But what if we also wanted to take a backup of the data at the same time as we delete it. Just incase we have made any mistakes. Well this is possible using the OUTPUT DELETED clause in mssql server using the following code. Of course you will need to create the table we are going to backup to just prior to running this. In this example we are using T as the main table and TBackup as our backup table that we are moving data to.

SET ROWCOUNT 5000
WHILE EXISTS(SELECT * FROM T WHERE N > 5000)
BEGIN
        INSERT INTO TBackup
                SELECT * FROM (
                        DELETE FROM T
                                OUTPUT DELETED.*
                                WHERE N > 5000
                ) a
END
SET ROWCOUNT 0

So now we have all the data we wanted out of the main table into a second table. This may be purged later or we are simply moving things into an archive table.