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.
Did You find this page useful?
Thanks for the feeback. Please consider sharing with others.