Category Archives: OUTPUT

Delete and archive bulk record from SQL Server table with OUTPUT clause

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.

1DeleteMessage

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.