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

成都创新互联公司成立于2013年，是专业互联网技术服务公司，拥有项目成都网站制作、做网站网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元南木林做网站,已为上家服务,为南木林各地企业和个人服务,联系电话:13518219792

英文原文：

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

让我们现在执行这个存储过程，它会需要一些时间，你会在屏幕上看到如下信息。

另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

本文名称：使用OUTPUT从句从SQLServer表删除和归档大量记录-创新互联
标题URL：http://kswjz.com/article/hdjpd.html