SQL 存档脚本 [英] SQL Archive Script

问题描述

我正在尝试将数据库中表中的记录归档到归档数据库中的相同表中.我需要能够对日期大于三年前的所有记录进行插入，然后删除这些行.但是，该表有数百万条实时记录，因此我想在一次大约 100 到 1000 个块的循环中运行它.到目前为止，我的存储过程执行整个插入语句，然后执行删除语句(在事务中)，其 WHERE 子句与插入语句基本相同.我的 WHILE 循环正在查找表中最早的日期以确定循环何时完成.其中一些似乎非常低效.有没有一种方法可以对记录块进行插入和删除，而不必在同一个循环执行中查找它们两次?有没有更好的方法来确定 WHILE 语句何时完成?运行 MS SQL Server 2000.

I'm trying to archive records from a table in a database to an identical table in an archive database. I need to be able to do an insert for all records with a date greater than three years ago, and then delete those rows. However, this table has millions of records which are live, so I want to run this in a loop of roughly 100 to 1000 chunks at a time. So far my stored procedure does the entire insert statement, then a delete statement (in a transaction) with essentially the same WHERE clause as the insert statement. My WHILE loop is looking for the oldest date in the table to determine when the loop is completed. Some of this seems pretty inefficient. Is there a way I can do an insert and delete on the chunk of records without having to look them up twice in the same loop execution? Is there a better way to determine when the WHILE statement is completed? Running MS SQL Server 2000.

这是我当前的程序(ISAdminDB 是主数据库，ISArchive 是存档数据库):

This is my current Procedure (ISAdminDB is the main DB, ISArchive is the archive DB):

    WHILE ( (SELECT MIN( [MyTable].[DateTime]) FROM  [ISAdminDB].[dbo].[MyTable]) < DATEADD(d, -(3 * 365), GetDate()))
BEGIN

INSERT INTO [ISArchive].[dbo].[MyTable] 
(<Fields>)
SELECT TOP 1000 (<Fields>)
FROM  [ISAdminDB].[dbo].[MyTable]
WHERE 
   [MyTable].[DateTime] < DATEADD(d, -(3 * 365), GetDate())
AND  UniqueID in (SELECT TOP 1000 UniqueID  FROM  [ISAdminDB].[dbo].[MyTable] ORDER BY [MyTable].[DateTime] ASC )

BEGIN TRAN
DELETE FROM  [ISAdminDB].[dbo].[MyTable]
WHERE   [MyTable].[DateTime] < DATEADD(d, -(3 * 365), GetDate()) 
AND  (UniqueID in (SELECT TOP 1000 UniqueID FROM  [ISAdminDB].[dbo].[MyTable] ORDER BY [MyTable].[DateTime] ASC))
COMMIT

END

推荐答案

首先，您要删除早于 3 年前的特定日期的记录.您不在乎删除它们的顺序，您只需要不断删除它们，直到没有任何剩余为止.您还可以通过使用临时表来存储 ID，并将截止日期存储在一个变量中并重复引用它来加快速度.

Firstly, you're deleting records earlier than a specific date, 3 years ago. You don't care what order they're deleted in, you just need to keep deleting them until there aren't any left. You can also speed things up by using a temporary table to store the IDs, and by storing the cut-off date in a variable and repeatedly referring to it.

所以现在我们有:

DECLARE @NextIDs TABLE(UniqueID int primary key)
DECLARE @ThreeYearsAgo datetime
SELECT @ThreeYearsAgo = DATEADD(d, -(3 * 365), GetDate())

WHILE EXISTS(SELECT 1 FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo)
BEGIN 
    BEGIN TRAN 

    INSERT INTO @NextIDs(UniqueID)
        SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo

    INSERT INTO [ISArchive].[dbo].[MyTable] (<Fields>) 
        SELECT (<Fields>) 
        FROM  [ISAdminDB].[dbo].[MyTable] AS a
        INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID

    DELETE [ISAdminDB].[dbo].[MyTable]
    FROM  [ISAdminDB].[dbo].[MyTable] 
    INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID 

    DELETE FROM @NextIDs

    COMMIT TRAN
END 

这篇关于SQL 存档脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！