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

how use begin transaction while loop in sql server?

this query never finishes perhaps because stops , commit transaction after inserting 1 row (when @cnt = 1) don't want commit transaction because want see results before committing.

begin transaction    declare @cnt int = 0;    while @cnt <= 100   begin       declare @offset int = 1        insert totalsales (col1, col2)           select                'col1', row_number() on (order col2) + @offset                          sales        set @cnt = @cnt + 1;   end; 

so how can check result before commit in while loop?

in same batch (within same transaction) can issue select command see updated content of table. changes persisted when commit transaction statement executed or reverted on rollback.

create table test (id int identity(1,1), x varchar(32)); go  begin transaction;  insert test (x) values ('a'); insert test (x) values ('b');  select * test;  rollback transaction; 

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

alternatively can use insert .. output construct output result of insert statement.

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

outside batch (using second connection), can use read uncommitted isolation level able read records not committed yet.

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