CODEWITHSUNDEEP
sql-servert-sqltransactions
shaadi
shaadi

Reputation: 171

How to use BEGIN TRANSACTION with while loop in SQL Server?

How to use BEGIN TRANSACTION with while loop in SQL Server?

This query never finishes perhaps because it stops and look for COMMIT TRANSACTION after inserting one row (when @cnt = 1) but I don't want to COMMIT TRANSACTION because I want to see results before committing.

BEGIN TRANSACTION 
  DECLARE @cnt INT = 0;

  WHILE @cnt <= 100
  BEGIN
      DECLARE @offset INT = 1

      INSERT INTO totalSales (col1, col2)
          SELECT 
              'Col1', ROW_NUMBER() OVER (ORDER BY col2) + @offset 
          FROM
              sales

      SET @cnt = @cnt + 1;
  END;

So how I can check result before commit in while loop?

Upvotes: 2

Views: 31274

Answers (3)

Joaquinglezsantos
Joaquinglezsantos

Reputation: 1620

You should create a BEGIN TRAN outer (general), and inside loop while create a BEGIN TRAN inner (with a trans name). Inside loop, if are conditions to rollbacks only for this iteration i use SAVE TRAN savepoint for not lose previous trans.

I 've created an example tests in loop while with conditional inserts and rollback savepoint:

declare @num int
set @num = 0

--drop table #test
create table #test (
    valor Varchar(100)
)
begin tran
while @num <= 5
begin
    begin transaction tran_inner
    insert into #test (valor) values ('INSERT 1 INNER -> ' + convert(varchar(10),@num))
    save transaction sv_inner
        insert into #test (valor) values ('INSERT 2 EVEN - SAVEPOINT -> ' + convert(varchar(10),@num))
        if @num % 2 = 0 begin
            commit transaction sv_inner
        end else begin
            rollback transaction sv_inner
        end
    insert into #test (valor) values ('INSERT 3 INNER -> ' + convert(varchar(10),@num))
    set @num = @num + 1
    if @@trancount > 0 begin 
        commit transaction tran_inner
    end
end
select valor from #test;
if @@trancount > 0 begin 
    commit tran
end

Return rows: 1, 2 if iteration even, and 3.

img returns tsql

Upvotes: 3

JMabee
JMabee

Reputation: 2300

If you are saying it never finishes it sounds to me like you are getting some blocking going on because that loop runs just fine.

https://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server/

I HIGHLY recommend using Adam Machanic's sp_WhoIsActive for this as well: http://whoisactive.com

Upvotes: 0

Pred
Pred

Reputation: 9042

In the same batch (within the same transaction) you can simply issue a SELECT command to see the updated content of the table. Changes will be persisted when the COMMIT TRANSACTION statement is executed or reverted on ROLLBACK.

CREATE TABLE test (id INT IDENTITY(1,1), x VARCHAR(32));
GO

BEGIN TRANSACTION;

INSERT INTO test (x) VALUES ('a');
INSERT INTO test (x) VALUES ('b');

SELECT * FROM test;

ROLLBACK TRANSACTION;

Example: http://sqlfiddle.com/#!6/e4910/2

Alternatively you can use the INSERT INTO .. OUTPUT construct to output the result of the INSERT statement.

Docs: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

Outside the batch (using a second connection), you can use READ UNCOMMITTED isolation level to be able to read records not committed yet.

Docs: https://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx

Upvotes: 1

Related Questions