将SQL Server数据移动到有限的(1000行)块中 [英] Move SQL Server data in limited (1000 row) chunks

问题描述

我正在编写一个过程，该过程基于datetime列对SQL Server表中的行进行存档.我想将所有带有日期的行移动到X之前，但是问题是每个日期都有数百万行，因此对每个日期进行BEGIN TRANSACTION ... INSERT ... DELETE ... COMMIT花费的时间太长，并为其他用户锁定数据库.

I'm writing a process that archives rows from a SQL Server table based on a datetime column. I want to move all the rows with a date before X, but the problem is that there are millions of rows for each date, so doing a BEGIN TRANSACTION...INSERT...DELETE...COMMIT for each date takes too long, and locks up the database for other users.

有没有办法可以做得更小一些?也许使用ROWCOUNT之类的东西?

Is there a way that I can do it in smaller chunks? Maybe using ROWCOUNT or something like that?

我本来考虑过这样的事情:

I'd originally considered something like this:

SET ROWCOUNT 1000

DECLARE @RowsLeft DATETIME
DECLARE @ArchiveDate DATETIME

SET @ROWSLEFT = (SELECT TOP 1 dtcol FROM Events WHERE dtcol <= @ArchiveDate)

WHILE @ROWSLEFT IS NOT NULL
BEGIN

    INSERT INTO EventsBackups
    SELECT top 1000 * FROM Events

    DELETE Events

    SET @ROWSLEFT = (SELECT TOP 1 dtcol FROM Events WHERE dtcol <= @ArchiveDate)

END

但是后来我意识到我不能保证我要删除的行就是我刚刚备份的行.或者我可以...?

But then I realized that I can't guarantee that the rows I'm deleting are the ones I just backed up. Or can I...?

更新: 我考虑过的另一个选择是添加一个步骤:

UPDATE: Another options I'd considered was adding a step:

1. 在临时表中选择满足我的日期条件的前1000行
2. 开始交易
3. 从临时表插入存档表
4. 从源表中删除，并跨每一列加入到临时表中
5. 提交交易
6. 重复1-5，直到没有剩余符合日期条件的行

有人对这个系列的费用与下面讨论的其他选项相比有什么想法吗?

Does anybody have an idea for how the expense of this series might compare to some of the other options discussed below?

详细信息::有人问我，我正在使用SQL 2005.

DETAIL: I'm using SQL 2005, since somebody asked.

推荐答案

只需插入DELETE的结果:

Just INSERT the result of the DELETE:

WHILE 1=1
BEGIN

    WITH EventsTop1000 AS (
    SELECT TOP 1000 * 
        FROM Events
      WHERE <yourconditionofchoice>)
    DELETE EventsTop1000
        OUTPUT DELETED.* 
        INTO EventsBackup;

    IF (@@ROWCOUNT = 0)
        BREAK;
END

这是原子且一致的.

这篇关于将SQL Server数据移动到有限的(1000行)块中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！