我可以在 while reader.read 循环中间从 sql server 表中删除记录吗

Question

我正在尝试使用 SQL 数据读取器将记录从一个表移动到存档表中。我想逐条记录地传输（而不是批量插入和删除），以便如果发生错误，丢失的数据量最少（并且必须从备份中恢复）。

我的问题是，如果我在读取记录后删除记录，SQL 阅读器会工作吗？这是迄今为止最简单、最直接的方法来完成我想做的事情。它会打开意外错误吗？

While myReader.Read // select * from tableA where older than 1 year record
    insert the record into tableA_archive
    delete the record from tableA   
loop

这似乎隐约类似于循环遍历您在循环中修改的列表——我知道这会导致 .net 中的各种令人头疼的问题。

for aItem in listA
   if aItem fits criteria, send to list B and delete from listA
next

当读者阅读时，有什么理由不修改基础表？例如，如果我在循环中间开始删除记录，读者是否会使用某些索引来访问可能会发生变化的记录？

Answer 1

您应该避免将行一直传输到客户端，而只是将它们重新上传回服务器。相反，执行如下操作（在同一事务内）：

INSERT INTO NEW_TABLE SELECT * FROM OLD_TABLE WHERE condititon;
DELETE FROM OLD_TABLE WHERE condititon;

或者甚至是 MS SQL Server 特定的：

DELETE FROM OLD_TABLE OUTPUT DELETED.* INTO NEW_TABLE WHERE condition;

（所有条件都相同）

如果您愿意，您甚至可以从 .NET 代码中执行这些语句，并且仍然保留不下载所有行的好处。

Answer 2

您应该花一些时间阅读事务隔离，特别是可重复读取。

在 SQL Server 中，select 语句（支持您的读取器）（默认情况下）在 READ COMMITTED 隔离中执行，这意味着您的删除命令将能够在读取行后删除这些行，但行集当执行 select 语句时，读取器读取的数据不能被删除语句修改。

无论如何，将数据从一个表移动到另一个表通常应该在 SQL 中完成，而不是在 .NET 中完成。

Answer 3

有几种方法，首先你必须了解什么是事务隔离
在 SQL Server 中以及什么时候需要使用它。

在您的情况下，似乎您正在将历史数据移动到另一个表，因此在事务范围内使用 SQL 存储过程（带有回滚事务）是值得的。

Answer 4

您应该在事务中执行此操作。这也是一个非常好的 SP 候选者，尽管您也可以在代码中做到这一点。您必须找到一个能够产生相当数量的行的条件（不是 1，而是可能是 100、1000 或 10000）。实验并找出在合理的时间内（可能是几秒钟）在一个语句中可以插入和删除多少个。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN
    INSERT INTO tableA_archive (col1, col2)
        SELECT col1, col2 FROM tableA WHERE (SomeCondition)
    DELETE FROM tableA  WHERE (SomeCondition)
COMMIT TRAN

SomeCondition 可以是日期范围（例如一天、一小时等）。

然后循环运行此过程，更改 SomeCondition，直到没有任何内容可备份。由于这是在事务内，因此插入和删除都会成功或失败。

不管你想一次删除多少个，如果你关心一致性，你就必须在一个事务中完成。