在有限的(1000 行)块中移动 SQL Server 数据

2022-01-09 00:00:00 insert sql-server rowcount

我正在编写一个基于日期时间列归档 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

这是原子的和一致的.

相关文章