Shulou(Shulou.com)06/03 Report--

Delete and archive a large number of records from the SQL Server table using OUTPUT clauses

Original English text:

Http://blog.extreme-advice.com/2013/01/08/delete-and-archive-bulk-record-from-sql-server-table-with-output-clause/

A friend and old colleague of mine on Facebook, a team leader of a financial product, asked me what he should do if he had a table with millions of records and wanted to file some records from it.

A simple DELETE statement produces a large number of log files (if the database is not a simple recovery model), locks the table for a very long time, consumes a lot of resources, and greatly affects performance.

It's usually good to keep short transactions. I always like batch DELETE/UPDATE records, especially when there are a large number of records in the production environment, because it takes a long time to delete a large number of records in the Dalai Lama's transaction environment, and if you cancel the DELETE operation, it may take hours or a day, everything will be rolled back, and it will take a long time, if you delete in small batches, like 1000 lines each time, and you stop execution. You will only roll back a maximum of 1000 lines without taking long.

Let's show how to do this by creating a sample database.

-- Create sample databaseCREATE DATABASE ExtremeAdviceGOUSE ExtremeAdviceGO--create sample table along with 100000 rowsIF OBJECT_ID ('orders',' U') IS NOT NULL BEGINDROP TABLE ordersENDGOCREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT) GOINSERT INTO orders (OrderDate, Amount, Refno) SELECT TOP 100000DATEADD (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 aCROSS JOIN sys.all_objects bGOIF OBJECT_ID ('ordersArchive',' U') IS NOT NULL BEGINDROP TABLE ordersArchiveENDGOCREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT) GO

Now that we have prepared the sample environment, let's create a stored procedure to delete records based on the conditions we provide.

USE [ExtremeAdvice] GOCREATE PROCEDURE [dbo]. [SPordersArchive] ASSET NOCOUNT ONDECLARE @ cnt INT, @ rows INTSELECT @ cnt = 1DECLARE @ msg VARCHAR (1024) DECLARE @ dt DATETIME--creating infinite loop which will break itself whenever all record gets deleted based on condition givenWHILE 1=1BEGINSELECT@dt = GETDATE (), @ rows = 0--Deleting records in bunchDELETE TOP (1000) oOUTPUTdeleted.OrderID,deleted.OrderDate,deleted.Amount,deleted.RefNoINTO ordersArchive (OrderID,OrderDate,Amount,RefNo) FROM Orders AS o (NOLOCK) WHERE o.OrderID

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.