使用OUTPUT从句从SQL Server表删除和归档大量记录

网中网

　　使用OUTPUT从句从SQL Server表删除和归档大量记录
　　英文原文：
　　http://blog.extreme-advice.com/2013/01/08/delete-and-archive-bulk-record-from-sql-server-table-with-output-clause/
　　我Facebook上的一个朋友和老同事，他是一个财务产品的团队领导，问我他有一个几百万记录的表，想去从这个表归档一些记录，他该如何做？
　　简单的DELETE语句会产生大量的日志文件（如果数据库不是简单恢复模式），会锁住该表非常长时间，并消耗大量资源而极其影响性能。
　　通常保持简短的事务是非常好的，我总是喜欢批量DELETE/UPDATE记录，尤其是当有大量的记录在生产环境中，因为在达赖那个事务环境删除大量记录需要很长时间，如果你取消DELETE操作可能需要数小时或一天时间，一切将会被回滚，也将会花费很长时间，如果你小批量删除，像每次1000行，而你停止执行，你将只回滚最大1000行，而不用多长时间。
　　让我们通过创建一个示例数据库来展示如何实现。
--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
　　现在我们已经准备好了示例环境，让我们创建一个存储过程，基于我们提供的条件删除记录。
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
　　让我们现在执行这个存储过程，它会需要一些时间，你会在屏幕上看到如下信息。