none
@@TRANCOUNT Query on INSERT

    Question

  • Can anyone explain the following?

     

    Code Snippet

    create table trancount

    (dt datetime, trn int)

     

    select getdate(), @@trancount -- 0 here

     

    insert into trancount (dt, trn)

    select getdate(), @@trancount

     

    select dt, trn -- 2 here!!!

    from trancount

    drop table trancount

     

     

    I don't understand why the @@trancount is 2.

     

    Wednesday, November 12, 2008 4:44 PM
    Moderator

All replies

  • Hi,

     

    The insert statement is actually made up of two transactions, a selection that is subsequently placed into another transaction that performs the INSERT part.

     

    Code Snippet

    insert into trancount (dt, trn)

    select getdate(), @@trancount

     

     

     

    You can see that the above code block is responsible for two transactions by wrapping the section in an explicit transaction.

     

    Code Snippet

    create table trancount

    (dt datetime, trn int)

    go

     

    BEGIN TRANSACTION

    select getdate(), @@trancount -- 0 here

     

    BEGIN TRANSACTION

    insert into trancount (dt, trn)

    select getdate(), @@trancount

    COMMIT TRANSACTION

     

     

    select dt, trn -- 2 here!!!

    from trancount

     

    GO

    COMMIT transaction

    drop table trancount

    GO

     

     

    Wednesday, November 12, 2008 5:01 PM
  • @@trancount is typically used with explicit transaction not implicit.

     

    A.D.T.

    Wednesday, November 12, 2008 5:03 PM