Delete and archive bulk record from SQL Server table with OUTPUT clause
One of my Facebook friend and old colleague who is Team Leader for one financial product asked me that he has a table with millions of records and wanted to archive few million records from that table, how he can do it?
Simple DELETE statement would make the huge transaction log file (if database is not simple recovery mode), lock the table for VERY LONG time and consume too much resource which will ultimately affect performance.
It is always good to keep short transaction and hence I always prefer to delete / update records in bunch, especially when there is a question of million rows in live production environment because sometime deleting millions of records in highly transaction environment take very long time, may be few hours or a day and if you cancel execution of delete query, everything will be roll backed which will again take a long time and if you are deleting in small bunch, like 1000 rows at a time and you stop execution, you will have to roll back maximum 1000 rows which won’t take long time.
Let me show you how can we do it by creating one sample database.
--Create sample database CREATE DATABASE ExtremeAdvice GO USE ExtremeAdvice GO --create sample table along with 100,000 rows IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN DROP TABLE orders END GO CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT) GO INSERT INTO orders (OrderDate, Amount, Refno) SELECT TOP 100000 DATEADD(minute, ABS(a.OBJECT_ID % 50000 ), CAST('2012-02-01' AS DATETIME)), ABS(a.OBJECT_ID % 10), CAST(ABS(a.OBJECT_ID) AS VARCHAR) FROM sys.all_objects a CROSS JOIN sys.all_objects b GO IF OBJECT_ID('ordersArchive', 'U') IS NOT NULL BEGIN DROP TABLE ordersArchive END GO CREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT) GO
Now we have sample environment ready to use so let us create one stored procedure which will delete rows based on the condition we provide.
USE [ExtremeAdvice] GO CREATE PROCEDURE [dbo].[SPordersArchive] AS SET NOCOUNT ON DECLARE @cnt INT, @rows INT SELECT @cnt = 1 DECLARE @msg VARCHAR(1024) DECLARE @dt DATETIME --creating infinite loop which will break itself whenever all record gets deleted based on condition given WHILE 1=1 BEGIN SELECT @dt = GETDATE(), @rows = 0 --Deleting records in bunch DELETE TOP (1000) o OUTPUT deleted.OrderID, deleted.OrderDate, deleted.Amount, deleted.RefNo INTO ordersArchive( OrderID ,OrderDate ,Amount ,RefNo ) FROM Orders AS o (NOLOCK) WHERE o.OrderID <=50000 SELECT @rows = @@ROWCOUNT SELECT @cnt = @cnt + 1 SELECT @msg = 'Lap : ' + CAST(@cnt AS VARCHAR) + ' ARCHIVED ' + cast(@rows AS VARCHAR) + ' rows in ' + cast(DATEDIFF(second, @dt, GETDATE()) as varchar) + ' seconds' RAISERROR(@msg, 0, 1) WITH NOWAIT IF @rows = 0 BREAK; WAITFOR DELAY '00:00:00.100' END
Let us now execute the stored procedure, it may take sometime and you will get message later on which will looks something like following screen capture.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.