sql-server - 提交sql中的每条x记录

Question

我来自 oracle 背景，我对 SQL Server 有点困惑。

我想阻止 SQL Server 在我的插入命令中自动提交。所以我正在使用 begin transaction。在下面的演示中，我将 100 行插入到测试表中，我将在事务结束时回滚。我期望的结果是 90 行(提交应该在 30、60 和 90 行)，其余 10 条记录应该回滚。

CREATE TABLE test (TEST_ID INT);
GO

DECLARE @cnt INT=0;
BEGIN TRANSACTION t1;

WHILE @cnt < 100
BEGIN
  INSERT INTO test values (@cnt); 
  SET @cnt += 1;

  if (@cnt %30 = 0)
    print 'commit '+cast(@cnt as varchar(100))
    COMMIT TRANSACTION t1;
END

rollback transaction t1;
GO

首先，我确保我的 SQL Server Management Studio 自动提交已关闭，为此我遵循以下步骤:

Tools > Option > Query Execution > Sql Server > Ansi > checked the SET IMPLICIT_TRANSACTION

在那之后我运行了脚本，但我得到了错误的结果和一些错误。

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

行数为100!

我尝试关闭我的连接并在 oracle 中再次打开它 bcs 当我插入假设 100 行而不提交最后假设 10 行时，其他人可以看到 90 行，但我看到 100 行，因为它是已经保存在我的 session 中。但看起来 SQL Server 在 session 和事务方面的行为有所不同。

我遇到的另一个问题是关于 while 循环内的提交，看起来它无法访问循环外的开始事务。当我运行命令时，我没有选择任何东西。只需按F5。即使我尝试选择命令并运行，但它仍然给我错误:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

我对 SQL Server 越来越着迷，我可以很轻松地使用 mysql 和 oracle，但是 SQL Server ....

Answer 1

实际上，Sql Server 不支持嵌套事务。它将允许您开始嵌套事务，但不允许到处回滚。实现此目的的唯一方法是使用保存事务语句。您可以在循环中向事务添加一些保存点，然后在循环外您可以回滚到该保存点并提交事务。这是一个例子:

DECLARE @cnt INT=0
Begin transaction

WHILE @cnt<100
BEGIN
  INSERT INTO test values (@cnt)
  SET @cnt+=1

  if (@cnt%30=0)
  begin
      save transaction t1
  end
END;

rollback transaction t1;
commit;

Select * from test

输出是从 0 到 89。

这是 fiddle http://sqlfiddle.com/#!6/07af4/37