使用OUTPUT從句從SQLServer表刪除和歸檔大量記錄

使用OUTPUT從句從SQL Server表刪除和歸檔大量記錄

10年積累的網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè)經(jīng)驗(yàn)，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識你，你也不認(rèn)識我。但先網(wǎng)站設(shè)計(jì)制作后付款的網(wǎng)站建設(shè)流程，更有工布江達(dá)免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

英文原文：

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

我Facebook上的一個(gè)朋友和老同事，他是一個(gè)財(cái)務(wù)產(chǎn)品的團(tuán)隊(duì)領(lǐng)導(dǎo)，問我他有一個(gè)幾百萬記錄的表，想去從這個(gè)表歸檔一些記錄，他該如何做？

簡單的DELETE語句會產(chǎn)生大量的日志文件（如果數(shù)據(jù)庫不是簡單恢復(fù)模式），會鎖住該表非常長時(shí)間，并消耗大量資源而極其影響性能。

通常保持簡短的事務(wù)是非常好的，我總是喜歡批量DELETE/UPDATE記錄，尤其是當(dāng)有大量的記錄在生產(chǎn)環(huán)境中，因?yàn)樵谶_(dá)賴那個(gè)事務(wù)環(huán)境刪除大量記錄需要很長時(shí)間，如果你取消DELETE操作可能需要數(shù)小時(shí)或一天時(shí)間，一切將會被回滾，也將會花費(fèi)很長時(shí)間，如果你小批量刪除，像每次1000行，而你停止執(zhí)行，你將只回滾最大1000行，而不用多長時(shí)間。

讓我們通過創(chuàng)建一個(gè)示例數(shù)據(jù)庫來展示如何實(shí)現(xiàn)。

--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

現(xiàn)在我們已經(jīng)準(zhǔn)備好了示例環(huán)境，讓我們創(chuàng)建一個(gè)存儲過程，基于我們提供的條件刪除記錄。

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

讓我們現(xiàn)在執(zhí)行這個(gè)存儲過程，它會需要一些時(shí)間，你會在屏幕上看到如下信息。

當(dāng)前題目：使用OUTPUT從句從SQLServer表刪除和歸檔大量記錄
網(wǎng)頁URL：http://bm7419.com/article26/geiejg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、網(wǎng)站設(shè)計(jì)公司、品牌網(wǎng)站建設(shè)、App設(shè)計(jì)、品牌網(wǎng)站制作、關(guān)鍵詞優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)